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

Question

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!

DawnDuong · Answer

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.

Peachyco · Answer

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.

Ar13f · Answer

dear @JuanGaspar

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