String to Double issue

[Usamah22]

Hello,

i have a field with sales volumes such as 2,500 which is set as string when alteryx imports the input file. when i use the select tool and convert the field to a double it cuts the figures at the comma so the 2,300 becomes 2. I need to summarise and do calculations so can't keep the field as a string. How do i correctly convert the field to an integer?

[jvansistine · Accepted solution]

Hi @Usamah22 ,

You could try using a data cleansing tool to remove any punctuation from your sales volume column before you change the data type.  You could also do that with a formula tool and the REPLACE function.

Removing the commas from the string should allow the conversion to work.

-Jim

[CharlieS]

I might suggest removing the comma character while the field is still a string. Something like this:

Replace([Field],",","")

This idea is that you're replacing the comma character with nothing (""), effectively deleting it. Now you can convert like you did before, or do this all in one Formula:

ToNumber(Replace([Field],",",""))

If you use a standard Formula tool, you'll have to apply this formula in a new field to change type. Otherwise you could use a Multi Field Formula tool and apply this formula and change type all in one tool. 

[echuong1]

The commas make the value a string (text) and therefore anything after the comma would be truncated by just changing the datatype to a number. You'd need to remove the commas first.

In the example, I used a formula to remove the commas, and then a Select to change the datatype. Another way of achieving this, especially if there are multiple columns that need to be adjusted, is to use a multi-field formula. This will allow you to deploy the cleansing formula to multiple values at once, and change the datatype all in one.

A third option is to use a Data Cleansing to remove punctuation, and then a Select to change the datatype. This works especially well if you have additional characters you need to remove (ex: $).

Hope this helps!