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
Solved! Go to Solution.
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? :)
NJ
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
You can try this code:
Replace(Replace(Replace(Replace([Value],".",'C'),",",'D'),"C",','),"D",'.')
It nests the following logic:
Replace Decimal Points with 'C'
Replace Commas with 'D'
Replace C's with a comma
Replace D's with a decimal point
1,000 becomes 1.000
1.000 becomes 1,000
1.000,000 becomes 1,000.000
Cheers,
Mark
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.
Dear Joe_Mako,
yeah! that was the solution.
Thank you so much. It worked exactly as you suggested.
best
Crete
Version 11.3 now has this built into the Select tool, along with the conversion functions ToNumber() and ToString() to go along with their more international language support, see Key Features at http://downloads.alteryx.com/Latest.htm