This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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.
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.
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 viadecimalSeparator.