We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

String to Double issue

Usamah22
8 - Asteroid

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?

3 REPLIES 3
jvansistine
9 - Comet

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
17 - Castor
17 - Castor

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
Alteryx Alumni (Retired)

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!

 

echuong1_0-1612797071492.png

 

 

Labels