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

[dataviolet]

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.

[lmorrell · Accepted solution]

Hi @dataviolet 

Workflow is attached.

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!

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