Rounding weird when Select to Double

Hello,

I'm trying to import an TXT file but I'm having some issues in the SELECT part...the issue is that when I change my data type from String to Double, it changes the value itself.

Image below how it's supposed to be: 1 billion

How it's showing: 1,3K

Would anyone, please, know how can I solve it?

I've attached the TXT file below.

Thanks,

12.2020 client.txt

Input

Best Practices

Data Investigation

Accepted answers

LeandroDamato

@apathetichell and @marcusblackhill

I mixed your suggestions and got it right!

Just to clarify some alternatives you gave me...

Changing decimal in from comma to period in user settings - Nothing changed...the number went from 1.376 to 1,376.

Using select and checking the box to "Use comma" - It went from 1.376 to 1.

SOLVED: What I did was a ToNumber(Replace([Field], ".", ""), 1, 1, ",")...I think Alteryx is messing up with the decimals (if I choose comma in the ToNumber formula it shows as dots as and vice-versa).

Thanks for the help!

All comments

apathetichell

You localization setting is set up to have "." as your decimal separator - not comma. You need to adjust this under user settings/localization. Also - I find it easier to convert using tonumber() either in a new column or a multi-field formula. But your major problem is that your numbers have been AMERICANIZED.

LeandroDamato

Unfortunately, I've tried it before and it didn't work too )=

apathetichell

Quick other question - I notice that there may be characters on the right of this column - can you try trimming them and then converting in a multi-field formula via tonumber([_currentfield_]) or tonumber(regex_replace([_currentfield_],"([\d\.,-]+)","$1")) might help...

I can't tell if this is a localization issue because your other number columns are still being stored as text... Can you post a screen grab of your user settings? It's probably good just to rule that out.

LeandroDamato

Hey @apathetichell

In my input I've delimited and separated the "D" and "C" that follows the numeric sequence (1st image has a red line on the right side of the numbers) so it separated the numerics in one column and the letters in another one. My problem is when I try to convert it to double by a Select tool or importing it I have the option to turn to double in the fixed width menu but or even trying to convert in a formula with a ToNumber(), the results are the same.

Here's the Localization settings that I normally use and I've tried messing around inverting the commas and decimals of this settings too.

Thanks for the help!

apathetichell

your localizing settings are definitely wrong for this data.

You need to change thousand separator to period and decimal separator to comma. The string is being converted correctly in this setting. what happens when you change the settings and run the workflow? do you get the same number in red as 1,376? Also - you do need to remove any characters from the number prior to converting... I'd strongly recommend not converting in a select tool and either using a multi-field formula or creating a new double field in the formula tool and converting with tonumber([fieldname]) after dropping any characters...

Can you post what it looks like if you do that after changing the localization settings? also note - you probably will need to run the workflow after you change the settings.

if that doesn't work - try posting the workflow you have - not just the data. there could be a problem in the workflow itself.

LeandroDamato

@apathetichell and @marcusblackhill

I mixed your suggestions and got it right!

Just to clarify some alternatives you gave me...

Changing decimal in from comma to period in user settings - Nothing changed...the number went from 1.376 to 1,376.

Using select and checking the box to "Use comma" - It went from 1.376 to 1.

Thanks for the help!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335