In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

formatting numbers; which string type?

EtamSoko
8 - Asteroid

hi everyone,

 

I would appreciate a little help around formatting numbers in my dataset. 

 

I have original data in such format as per the attachment. The final product should be a sum of items with the same record ID. I know the way of getting that but the problem is when I set the string type to Double, I am loosing the decimals. If I keep it on V_String , then I get the shown wierd number. I tried other string types but none of them worked perfectly. 

 

What am I missing here?

 

I appreciate your help in advance.

thanks,

MateK

8 REPLIES 8
AbhilashR
15 - Aurora
15 - Aurora

Hi @EtamSoko, you could use the data cleansing tool to get rid of leading and trailing whitespaces, and then use a formula tool to replace comma with decimal, after which you can sum up the data using summarize tool. Something like the attached solution. 

AbhilashR_0-1587162333219.png

 

EtamSoko
8 - Asteroid

@AbhilashR thanks for the reply. It is not fully resolved by ToNumber function. Once I apply your suggested solution many fields changes as per below:

 

BEFORE 

                                 

47.470,50                                 
47.470,50
47.470,50-
47.470,50-
17.000,00
630,00


AFTER

 

47.47
47.47
47.47
47.47
17
630

 

the string type is still V_string as before. I can't neither lose the visbility on decimals, nor the minuses.

 

What is the rule here I am a bit confused why this is happening.

 

thanks,

MateK

RolandSchubert
16 - Nebula
16 - Nebula

Hi @EtamSoko ,

 

I think, the samples you provided in your first post are correctly converted by the workflow @AbhilashR provided.

 

The new samples (output from SAP?) are a bit different (trailing sign, comma as decimal separator). I attached a workflow to convert these values to double data types.

Let me know if it works for you.

 

Best,

 

Roland

EtamSoko
8 - Asteroid

@AbhilashR thank you

@RolandSchubert  correct, that is SAP data and your solution works just perfect. Thanks for the help as always 🙂

EtamSoko
8 - Asteroid

hi Roland, I just got to the point where I could fully validate the result of your solution. The only problem I face with that due to the data quality there are number with the following format BEFORE the solution:

 

538.5
545
538,50

 

and this is how they look after:

 

5385
545
538.5

can you please help in amending the formula accordingly?

 

I tried to use if item contains both "." and "," then make the changes but my statement with tonumber just doesn't work out...

 

thanks a million!

MateK

EtamSoko
8 - Asteroid

@RolandSchubert if you have spare few minutes I would really appreciate you could take a look at the formula. thanks !

RolandSchubert
16 - Nebula
16 - Nebula

Hi @EtamSoko ,

 

so in your source data both "." and "," are used as decimal separator? An approach could be to check the format and decide based on "best guess". 

A rule could be:

- If you find both "," and "." in a number, the first you find in the reversed number is the decimal separator

- If you find one of the separators, it is a decimal separator, if there are less than three digits behind the separator

I've modified the formula accordingly - but that's of course not entirely reliable.

 

What do you think?

 

Best,

 

Roland

 

EtamSoko
8 - Asteroid

@RolandSchubert 

 

Correct, some SAP report apparently contains . and , as decimal separator. This was new for me too but I did a bigger validation on the data after applying your solution and it looks very good. Definitely taking away a few good tricks for future workflows that I would probably not figure out myself.

 

Really appreciate your time and effort.

 

Have a nice weekend,

MateK

Labels
Top Solution Authors