Number format changes when I switch from "V_String" to "Int64"
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
