Something really weird going on when comparing identical numbers (2000 != 2000) ?!?

[jt_edin]

I'm testing a workflow and encountered a really weird problem where Alteryx is saying a filter condition = false, but I can see plainly that the condition should = true.

I've extracted the single row of data which looks like this:

The three fields on the right should all = 2000. According to Select they are all Double types, 8 bytes, and their value is 2000:

And yet, mysteriously, when I evaluate this filter condition, Alteryx insists that 2000 != 2000. What gives?!?

[Start Point Time], [End Point Time] and [Frame] were all created in different ways earlier in the workflow, but all are doubles and all have a value of 2000. So why is Alteryx saying that [Frame] != either of the other two? It's messing with my head. Example zipped and attached, grateful for advice because this is really weird.

[echuong1 · Accepted solution]

It has to do with the way numbers are stored and their levels of precision.

When you have a number stored as a double, the number uses 8 bytes and can represent values from +/- 1.7 x 10-308 to 1.7 x 10308 with 15 digits precision. That being said, your values are slightly off with additional digits. If you cast your values as a fixed decimal with 25.15 you're able to see these digits. 

This is why joining on numeric values casted as a Double or Float are not advised. Try rounding your numbers to integers or fixed demical.

[jt_edin]

Thank you @echuong1 ! I am vaguely aware that binary floating point can't represent exact numbers like 0.1, but I have never experienced a scenario where Alteryx says it's 2000 but actually there are two numbers on either side of 2000. I guess you learn something every day.

I use filters ALL THE TIME for non-integer values, so can I ask how do other users deal with this? Do they recast just before filters and joins, or do they use fixed decimal throughout? Fixed decimal is kinda ugly if used throughout the workflow, but I guess that's because Alteryx rounds doubles automatically.

Basically, what does everyone else do? Thanks