Changing data type changed my value

Question

Hi everyone,

I'm working on this file that involves feeding numbers into a predictive model. The model wasn't performing as I imagined it would so I decided to take a sample and investigate some of my records.

I found that the amount in my output file didn't match the input file I was feeding into my workflow. After some digging around, I discovered that setting the data type to float changed the actual number in some of the rows. I filtered for for a sample and checked. In one of the rows, the amount going into the select tool was 44860857.16 (no commas) and the amount coming out of the select tool was 44,860,840 (which is the same as the number in the row above).

Changing the data type of the field to double fixed the issue, but I'm struggling to understand why setting the data type to float would do this when it's well within the bounds of min an max values supported by floats.

Any and all help would be much appreciated

Thanks!

m_abdelghaffar · Answer

Ah, okay. I was going off the min and max values in scientific notation and thought the digits precision applied to digits after the decimal point when reading the documentation

Thank you so much for your help, it really clears things up :)

mattnason1 · Answer

It seems like you just have string data in a field you are converting to numbers.

Try this replace before the select:

replace(replace(replace(Replace([ amount_lc ], ",", ""), "(", "-"), ")", ""), " ", "")

It's removing spaces, removing commas, removing closing parentheses and replacing opening parentheses with a - sign.

MTecovis · Answer

The Float does not "change" your value, it simply is a 4-byte value which has it's precision on 7 digits.

If you try it out you will recognize that every number over 7 digits before the divider comes in will be rounded on the 7th digit followed by only nills afterwards.

For Example:

123456789 -> 123456800

(rounded on the 7th digit, followed by nills)

The double value is a 8-byte value with a precision of 15 digits, that's why it keeps your data in that case right.

See the value description from Alteryx in the screenshot:

Hope this helps :)

Kind regards