We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Number format changes when I switch from "V_String" to "Int64"

JuanGaspar
5 - Atom

Hello, 

 

I'm currently trying to change the format of a column to be read as a number rather than a string. However when i use some of the numeric type formats my numbers will change from "1,862,541" to just "1". Does anyone know what would be the best data type to keep the same number format? I have already tried "Int64,32,16" and "double" and none of them seem to work. 

 

I just started learning Alteryx as part of a class, any feedback will be greatly appreciated. Thank you!

 

JuanGaspar_0-1634169951108.png

JuanGaspar_1-1634170001017.png

 

JuanGaspar_2-1634170028641.png

 

3 REPLIES 3
Ar13f
9 - Comet

dear @JuanGaspar 

 

I usually use the "data cleansing" tool to remove commas from numbers

 

Solution 211014 00.png

 

Solution 211014 01.png

Peachyco
8 - Asteroid

Like @Ar13f said, you can use the "Data Cleansing" tool. Put it before your "Select" tool to remove the commas in your VALUE column.

 

That solution should be enough since you seem to be dealing with integers/whole numbers. If you have to convert numbers with decimals, you can use the "Formula" tool instead. There's a function called REGEX_Replace that would allow you to remove just the commas and leave the decimal points in.

DawnDuong
12 - Quasar
12 - Quasar

hi @JuanGaspar 

the function "ToNumber" does this very elegantly and works for numbers with decimal places too.

 

ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number.

Examples

ToNumber("878") returns the string 878 as a number.

ToNumber("4.256411411E9") returns the string 4256411411 as a number.

ToNumber("Number", "false") returns 0 with conversion error: TONUMBER: Number lost information in conversion.

ToNumber("Number", 0, 0) returns 0 with conversion error: TONUMBER: Number lost information in conversion.

ToNumber("Number", 1, 0) returns 0 with no conversion error.

ToNumber("Number", 1, 1) returns [Null] and no conversion error.

ToNumber("123456,789", 1, 1, ",") returns 123456.789 as a number.

ToNumber("123.456,789", 1, 1, ",") returns 123456.789 as a number. This is because the period is automatically interpreted as the thousands separator, while the comma is specified as the decimal separator via decimalSeparator.

 

If your work often involves data conversion, check out this helpful resource: https://help.alteryx.com/20213/designer/conversion-functions

 

Dawn.

Labels