Data Gets Extra Decimal Numbers

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.

Data Investigation

Accepted answers

caltang

Hi @ricoo,

You can do the following for it to be dynamic:

Add a record ID to the data.
Then use Filter -> Custom Filter with REGEX_Match([Field], ".*\d+.*")
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.
Use a UNION tool to combine FALSE from No. 2, and combine with No. 3's output.
Then sort the Record ID asc.
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.

All comments

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

InputNumberAsString.yxzp

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

Hi @ricoo,

You can do the following for it to be dynamic:

Add a record ID to the data.
Then use Filter -> Custom Filter with REGEX_Match([Field], ".*\d+.*")
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.
Use a UNION tool to combine FALSE from No. 2, and combine with No. 3's output.
Then sort the Record ID asc.
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.

InputNumberAsString.yxzp

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!

anni6shah

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703