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

Alteryx reads wrong data

liketheway520
6 - Meteoroid

Hello everyone, I have a weird situation where alteryx reads the wrong data from the file I input to it, not sure if my file has the wrong format or something, but this keeps happening recently. Hopefully, someone could provide an answer.

 

Example: A product code from an excel file as 682.014, but alteryx reads as 682.01400000000001, is there a way this can be fixed? This field was set to V_String. Thank you so much.

9 REPLIES 9
CharlieS
17 - Castor
17 - Castor

So it's reading the data, just in an incorrect format? There are a few ways to correct this formatting, but the best will depend on the potential values ranges of the "Product Code". 

 

For example: If the code are always "###.###", then use a RegEx tool with the following expression: (\d{3}\.\d{3})

 

If you have the option of saving the input data as a csv from Excel, that might yield more consistent formatting. 

liketheway520
6 - Meteoroid

Hi Charlies, thank you so much for the information and attachment. Some of them were fixed but some codes were changed. I think because the format is not always "###.###", some are "###.###.###" 

liketheway520
6 - Meteoroid
 
ThizViz
11 - Bolide

I don't know if this is what's happening for you, but it's exactly like something that was happening to me. I spent a LOT of time trying to figure it out to no avail. For instance, even if I changed the number to specifically have 2 decimal places, it wouldn't work.

 

In the end, it was due to NULL values in my number field (the problem was discovered by Alteryx support personnel).

 

Once we tossed an IFNULL then 0 formula, no more problems.

 

Just a suggestion since you're problem is unfortunately very familiar to me.

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
CharlieS
17 - Castor
17 - Castor

I see. Well here's a little more RegEx to help this out.

 

I see that any MATL_NUM value that needs to be replaced has a lot of repeating "0" or "9" characters. This IF statement can be used to fund if the string has 7 or more "0" or "9" characters in a row. If it does, it replaces the value with the results from the first formula. 

 

 

liketheway520
6 - Meteoroid

Thank you for your help!

liketheway520
6 - Meteoroid

much appreciated! It worked finally. 

liketheway520
6 - Meteoroid

Just curious, do you know why Alteryx was reading those repeating "0" and "9" for some of numbers? Is it because the format was wrong?

CharlieS
17 - Castor
17 - Castor

Happy to help!

 

The extended values were in the Excel file provided so Alteryx read them exactly as they were saved. I don't think anything was done wrong in Alteryx, but it saved the day anyways. :D

Labels