Rounding weird when Select to Double

[LeandroDamato]

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,

[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 )=

[marcusblackhill]

Hi @LeandroDamato !

In the select tool, you have a check box at the very bottom of the configuration , that option will change the dot to comma as delimiter to numeric data types.  You already tried that?

Hope that helps

[LeandroDamato]

Hey, @marcusblackhill  !

Yeap, I've already tried it before and it didn't work too. 😢

Thanks,

(Sou BR tbm kkkkk)

[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.

[marcusblackhill]

@LeandroDamato 

Alright, so one suggestion I give to you is to put a formula tool before the select tool and use the formula Replace([Field_1], ".", "") in the field you have this issue (also, dont forget to keep the checkbox at the bottom marked)

[LeandroDamato · Accepted solution]

@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!