Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Issue with Data field types

Crete
7 - Meteor

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

6 REPLIES 6
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

Crete
7 - Meteor

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

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Joe_Mako
12 - Quasar

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

 

 

Crete
7 - Meteor

Dear Joe_Mako,

 

yeah! that was the solution. 

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

 

best

Crete

Joe_Mako
12 - Quasar

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

Labels