Some data with a dot in the middle gets extra decimal numbers when read as a .xlsx input file in Alteryx. It seems like it is being treated as a decimal value even though the column is a string.
I needed to explicitly save the file as a .csv so that it won't have this issue when being read in Alteryx. But this shouldn't be the case.
Solved! Go to Solution.
Hi @ricoo ,
Thanks for the observation. This is interesting.
I guess Excel stores the numeric data as Floating Point data, and passes it to Alteryx as it is, and the rounding error is observed at Alteryx...
If that is the case, you may need to store the numbers as String data.
To tell the number is a String data, I added a quote ['] before the number on Excel and put the file to Alteryx.
(The numbers aligned to Left have a quote at the head. The numbers aligned to Right are not changed.)
Then the data is shown as String on Alteryx.
So you may want to consider storing the data as String on Excel (if possible).
Hi @Yoshiro_Fujimori is there a way where in we can avoid the human intervention. I can see that in this case there really is a need to manually rectify those data in order for it to work.
Hi @ricoo,
You can do the following for it to be dynamic:
This is dynamic only if the column is always like that from Excel. So no manual intervention on the dataset required.
Hope it helps.
@ricoo ,
If you want to clean up the data after you get it on Alteryx, one solution is using Formula tool.
Formula
ProductCode2 =
IF REGEX_Match([ProductCode], "\d+\.\d+")
THEN ToNumber([ProductCode])
ELSE [ProductCode]
ENDIF
It seems the above formula works enough, but if you want to control the number of digits, you can add a function as below.
Product3 =
IF REGEX_Match([ProductCode], "\d+\.\d+")
THEN ToString(
ToNumber([ProductCode]), 3)
ELSE [ProductCode]
ENDIF
Output
I hope I am following your needs.
Thanks for sharing!
Hi @caltang , your process worked. I didn't follow all the exact steps you suggested, but the high level solution to set a RecordID, and isolate those that have decimal numbers has solved the problem. I made adjustments on the regular expression and use it as a filter; added a filter based on the length of the invalid ProductCode and isolate those.
Then apply the ToNumber() function to rectify the data; and join them back using UNION. Thanks a lot!
Thanks @Yoshiro_Fujimori this seems a very helpful solution as well. I've managed to solve my problem using @caltang 's formula but thank you for this. This is going to be very helpful when encountering similar problems in the future.
That's awesome! The tweak you made is quite cool, especially with the length of characters as another parameter for you to control. I'll apply that to my own workflow actually - thanks for responding! Cheers man.
Hello, I have also experienced the same issue.
For the data we were using there was a header section to the .xlsx file which contained strings, then below the first 3 rows it was decimals. The way we overcame this was by starting import on row 4. Alteryx then brought in the values correctly as they were displayed in the excel file without the additional decimals. Then using another input tool and sample tool to bring in the first 3 rows as strings, and unioning the data back together, I managed to replicate the input file exactly without the extra decimals.
Just putting this out there as it may help someone :)