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?
I might suggest removing the comma character while the field is still a string. Something like this:
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:
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.
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: $).