Why identical double type values fail to join correctly?

Question

Recently completed WC377 and upon reconciling to the solution provided, i notice 10 records did not reconcile even though they had the exactly same values in all fields.  I noticed this was due to 1 column having a DOUBLE data type, which is not recommended.  However, the recommendation states that both columns must be DOUBLE while joining and that is the case.  So then, shouldn't these rows be correctly returned by the join instead of being spit out in the L and R outputs??

Again, the values (as far as what we can see in the screen) are exactly the same.  Thanks!

challenge_377_solution.yxmd

apathetichell · Answer

Hey - sorry -> I may not have been clear but your understanding is off.

fixed decimals display their value. They can do this to I dunno 39 or something units of precision. They is are values. Doubles are fractions. They are displaying a representation of their value. So take 1/7 -> this is a fraction. It may look like

0.1428571

but it's a fraction stored as 1/7. A fixed decimal of that amount is that amount. exactly.

If multiply a double of 1/7 which looks like the amount above by 7,000,000,000 you should see 1,000,000,000 -> if you multiple a fixed decimal by that amount you will see 999,999,700 -> you use doubles where memory is more important than precision.

You use fixed decimals where precision is more important. Do not join with Doubles. Alteryx tells you not to join with doubles. Please don't.

3I_ATLAS · Answer

Thank you both.  I appreciate the answers/collaboration!

(to be clear I don't currently face an issue.  I'm just trying to understand this quirky behavior)

I did some tests and I think the answer is actually 3 fold:

(1) it is due to the fact that doubles will have usually more decimal places than Decimal 19.4 or even Decimal 19.6.

(2) doubles will not actually display their underlying full value (which I think it's a mistake from Alteryx - the full length value should be displayed regardless) example below

(3) Doubles have a varying number of decimal places (15 digits max) - So it could be 123.012345678912 (3 int + 12 decimals) or 1.23012345678912 (1 int + 14 decimals)

So I understand if the numbers are actually different they will not join correctly.  However for the same exact number I'd expect them to join correctly, but they don't.  That's fine if that's an Alteryx quirk.  I just wonder why.

It still irks me that Alteryx shows you the value for a DOUBLE type field with 2 decimals only when in reality it is actually storing many other decimal places.

The double field below comes from an Alteryx yxdb data file, so maybe that's part of the reason as well.

Miraculously, if a decimal has a high enough precision, it will correctly join to a double, even if the double is not exactly the same.  Alteryx seems to round the decimal to match the double's precision.  See attached workflow.

doubleXDecimalJoins.yxmd

apathetichell · Answer

doubles are stored as fractions. They can appear to be identical but can be SLIGHTLY different. Use fixed decimal. I tend to use 19.6 - but this is a bit stylized.