Issue with Data field types

Dear Alteryx Community,

hopefully you can help me with the following issue.

I have built a simple workflow.

Data comes from customer, I cut off a few lines, reorganize fields, change data field types with formula, rename, summarize and then send to output. This is all cool. But, there is one file where this does not work.

Now this is my issue.

In this case, the original file is formatted in numbers, but Alteryx recognizes the file fields as text. Normally this is no issue. Change to double and there you go.

But, I am here in Germany where one thousand is formatted as 1.000. Alteryx convertes this to 1,000 (For us 1.000 = one thousand and 1,000= one). All numbers > than 1.000 are lost. I have tried so many different ways for a work around, but is does not work. Would you have an idea, how I can solve this issue?

I have added a small picture, explaining what I mean.

thank you

Crete

Alteryx_format issue_illustration.png

Transformation

Accepted answers

Joe_Mako

How about a Multi Field Formula tool for converting the strings to numbers like:

ToNumber(
ReplaceChar(
ReplaceChar([_CurrentField_]
,".","")
,",",".")
)

This will first remove all period, and then replace the remaining comma with a period. The multiple field formula allows you to convert multiple fields at once and change the data type.

multi field formula.png

convert.yxmd

All comments

NicoleJohnson

Can you do a formula to replace the "." with "," in your number values before you change the field types from text to double?

Something like this:

IF REGEX_Match([qty],"\d*[.]\d*") THEN REGEX_Replace([qty],"(\d*)(.)(\d*)","$1,$3") ELSE REGEX_Replace([qty],"(\d*)(.)(\d*)","$1.$3") ENDIF

Or if you're not as comfortable with RegEx:

IF FindString([qty],".") THEN ReplaceChar([qty],".",",") ELSE ReplaceChar([qty],",",".") ENDIF

Might need to get more creative if some of your numbers contain both "." and "," (i.e. if you have a number 1.234,567)... let me know if that's the case.

At any rate, the formulas above should swap your , and . so that the numbers are in the format you need them to be to correctly convert from text to double in Alteryx... hope that helps?

Crete

Dear Nicole,

thank you so much for your fast reply.

I was trying something similar as you have suggested, but not as smart. Let me dig into the RegEx, I do not know this yet. Then I will tell you if it works or not.

Just copying your suggestions does not work.
For the first suggestion, the numbers > one thousand are lost.
For the sesond solution many more numbers are lost.
So, I need to be creative as you suggested. I love that and would like to play around.

Will keep you posted

Crete

Joe_Mako

How about a Multi Field Formula tool for converting the strings to numbers like:

ToNumber(
ReplaceChar(
ReplaceChar([_CurrentField_]
,".","")
,",",".")
)

This will first remove all period, and then replace the remaining comma with a period. The multiple field formula allows you to convert multiple fields at once and change the data type.

multi field formula.png

convert.yxmd

Crete

Dear Joe_Mako,

yeah! that was the solution.

Thank you so much. It worked exactly as you suggested.

best

Crete

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703