formatting numbers; which string type?

[EtamSoko]

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

[AbhilashR]

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. 

[EtamSoko]

@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 · Accepted solution]

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]

@AbhilashR thank you

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

[EtamSoko]

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]

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

[RolandSchubert · Accepted solution]

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]

@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