Data cleansing tool giving warnings on fields it shouldn't be cleansing

[adm510]

I have a data cleansing tool on my workflow which only has string values checked, however it keeps maxing my results window with warnings on an Int64 field -- which it should not be cleansing (it is unchecked).  Also the warning message that it's giving is incorrect, these #'s are Int64 numbers, not doubles, so it shouldn't even be throwing the warning in the first place.

Is there anyway to hide these error messages, or do I need to use the formula tool to write my own cleansing script?

[Inactive User]

Those numbers cannot be Int64. Int64 would null those values if you attempted to convert them. They must be strings. I would advise using fixed decimal as the data type, then you shouldn't have any issues.

[adm510]

Hi @Inactive User

These for your suggestion, however these numbers are Int64, I verified none of them exceeded the max limit (although they do go right up to it).

https://help.alteryx.com/11.0/Reference/DataFieldType.htm

I also verified with a select statement before and after the cleansing tool that they were Int64 and not strings.  Just curious, what makes you think they are not Int64?

[adm510 · Accepted solution]

I decided to remove the data cleansing tool, problem solved.

[Claje]

Hi @adm510,

I recognize that removing Data Cleansing solved your issue, but I just wanted to post really quickly to say that I think the issue causing this is actually in relation to something behind the scenes in how Alteryx handles these numbers.

I tested this by taking the second value in your message above (-9223372036854775808) and putting it into an int64 field.

Then I used a formula tool on that field (Field1) and wrote the formula as [Field1] (effectively, replace Field1 with itself).

This produced the same message.  Notably, there was no observed loss of data quality (the number stayed the same after the fact) and the field is still an INT64 field.

Formula (10) Field1: -9223372036854775808 does not fit in the type Double

[adm510]

Hi @Claje

Yes, you're right, I get the same warning if I try and copy an Int64 to another Int64 field.

[Claje]

Thanks for testing!

I just made a post:

https://community.alteryx.com/t5/Data-Preparation-Blending/How-does-the-Formula-tool-handle-large-nu...

This details a couple of other behaviors that I observed with the INT64 field and very large numbers.  I'm hoping someone with more experience/expertise can weigh in and provide some details.