formatting numbers; which string type?
- 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
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
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@AbhilashR thank you
@RolandSchubert correct, that is SAP data and your solution works just perfect. Thanks for the help as always 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@RolandSchubert if you have spare few minutes I would really appreciate you could take a look at the formula. thanks !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
