formatting numbers; which string type?

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

alteryx help1.yxmd

Workflow

Accepted answers

RolandSchubert

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

Conversion.yxmd

RolandSchubert

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

Conversion_2.yxmd

All comments

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.

alteryx help1.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703