Alteryx Data Type Data Cleansing Decimals .39999999999 instead of .4

Hello,

I have the attached (simplified) sample data that, when opened up in excel, shows the right codes (e.g., 18.4) but when dragged into alteryx via input data tool, shows up as 18.3999999999, or 18.30000000000001.

I think the reason why is because this column contains both numerical type data (18.4) along with string data (L8.4)

What would be the most efficient way to cleanse the data so that it ties to what I see in the source data in excel? I thought about record id, separating out the numerical data types and string data types, using select tool to reformat the numerical values, and then unioning them back together, and sorting to original order, but wondered if anyone else had any simpler ideas? My workflow is already complicated enough, so I'm trying to limit the number of new tools I add.

sample data_code formatting.xlsx

Datasets

Accepted answers

lmorrell

Hi @dataviolet

Workflow is attached.

Alteryx Data Type Data Cleansing Decimals .39999999999 instead of .4.png

Your guess as to the numbers being read as a string and resulting in your value inconsistencies is right on the mark. However, reversing this logic by identifying cells with only numbers and decimals, converting it to a number, and then rounding the number will let us arrive at a solution. These steps can be configured in a single formula tool, and could even be incorporated in pre-existing formula tools you have for data cleansing.

The formula to cleanse the codes is below.

if regex_match([Code], '[\d\.]+') then round(tonumber([Code]), 0.1)
else [Code]
endif

Hope this helps!

Alteryx Data Type Data Cleansing Decimals .39999999999 instead of .4.yxmd

All comments

lmorrell

Hi @dataviolet

Workflow is attached.

Alteryx Data Type Data Cleansing Decimals .39999999999 instead of .4.png

The formula to cleanse the codes is below.

if regex_match([Code], '[\d\.]+') then round(tonumber([Code]), 0.1)
else [Code]
endif

Hope this helps!

Alteryx Data Type Data Cleansing Decimals .39999999999 instead of .4.yxmd

Tyro_abc

Is this similar to your problem?

Solved: Re: Data format Issue - (Excel to Alteryx) - Alteryx Community

Regards

Arundhuti

dataviolet

Thank you, @lmorrell that seems to have worked, and didn't require any manual changes to the actual source data file. The only thing I would say is the solution would probably not differentiate in a situation like 1.1 and 1.10, but thankfully, my source data does not have any ".10" items.

Thank you again for the solution!

dataviolet

@Tyro_abc yes, that is definitely the same problem, but it looks like they had resolved it by changing the source file directly, which I was trying to avoid.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703