Converting Text field to Interger

Comments

8 comments

  • Avatar
    Nathan Giusti

    Hey Karen, I'm not sure what you mean. 

    If you are certain your source data is type decimal you can change the type in the elasticube like so

    0
    Comment actions Permalink
  • Avatar
    Karen Rasnick

    Nathan,

    I failed to include these are text fields that contain dollar signs, commas and decimals. I tried using your examples, but neither decimal or integer worked.  Both options returned blank results.

     

    Do you have other suggestions?

    0
    Comment actions Permalink
  • Avatar
    Nathan Giusti

    The decimals and commas shouldn't an issue but the dollar signs might be. 

    It'd be better if you could strip them out of your data at the source but you can format it in the elasticube with a custom column

    Replace all will do a replace fin the string. Here's an example that removed the dollar sign from an entry and casts it to a float. 

    ToFloat(ReplaceAll([my val], '$', ''))

    0
    Comment actions Permalink
  • Avatar
    Karen Rasnick

    I tried the ToFloat option, but it's not returning the expected result. Some values look correct, while others are dropping digits.

    Example1: $6,832.22           Result: 6

    Example2: $365.41              Result: 365.41

    Example3: $126,764.75       Result: 126

    0
    Comment actions Permalink
  • Avatar
    Nathan Giusti

    Could you post a screen shot of the preview of your custom column so I can see what you did and what it results in. 

    0
    Comment actions Permalink
  • Avatar
    Karen Rasnick

    0
    Comment actions Permalink
  • Avatar
    Nathan Giusti

    It looks like the commas are causing an issue. Add another replace all to remove the commas from your values and see if that works. If it doesn't the best way to trouble shoot this is to take it step by step. 

    Make a field with just the replaceall function and see if that looks like what you want. if it is, see if you can just change the type of that custom column to decimal. If that doesn't work, create a new custom column off the one you did the replace all on to cast it to a float. By breaking these up into individual steps and being able to see the results we should be able to figure out where the issue is. 

    0
    Comment actions Permalink
  • Avatar
    Karen Rasnick

    Thanks for your help Nathan!  It take a couple of steps, but it's working now.

     

     

    0
    Comment actions Permalink

Please sign in to leave a comment.