Data Gets Extra Decimal Numbers

[ricoo]

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.

[Yoshiro_Fujimori]

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).

[ricoo]

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.

[caltang · Accepted solution]

Hi @ricoo,

You can do the following for it to be dynamic:

1. Add a record ID to the data.
2. Then use Filter -> Custom Filter with REGEX_Match([Field], ".*\d+.*")
3. This filter will get out all the numbers as TRUE. Then, use a formula tool after True with a new column, use ToNumber() and change data type to Fixed Decimal, 2dp. Then, you can use a multi-field formula to change it string.
4. Use a UNION tool to combine FALSE from No. 2, and combine with No. 3's output.
5. Then sort the Record ID asc.
6. You can use a Select tool to deselect the Record ID, then you can work with that data.

This is dynamic only if the column is always like that from Excel. So no manual intervention on the dataset required.

Hope it helps.

[Yoshiro_Fujimori]

@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.

[Raj]

Thanks for sharing!

[ricoo]

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!

[ricoo]

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.

[caltang]

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.