Choose Language Hide Translation Bar

Why can't I select certain numerical rows?

I’m having an inexplicable problem with a script, shortened version below:

Clear Globals();

New Table( "A", New Column( "I", numeric, continuous ) );

aggregateColumn = {};

For( j = 1, j <= 5, j++,

allCurrents = {};

For( k = 0.0, k <= 6.0, k += 0.1, Insert Into(allCurrents, k); );

Column("I") << Set Values(allCurrents);

Insert Into(aggregateColumn, As List(Column("I") << Get Values));

);

aggTable = New Table( "B",

New Column( "I", numeric, continuous, Set Values(aggregateColumn) )

);

Now, in the the resulting data table “B”, the command:

Current Data Table() << Select Where(I == 0.5);

Successfully selects the row with 0.5, but:

Current Data Table() << Select Where(I == 1.0);

Selects nothing!  Also won't select 0.0, 0.3, 0.4, 0.8, 0.9 and many others.  What’s going on here?

Thanks.

20 REPLIES 20
Super User

Re: Why can't I select certain numerical rows?

If you change the format of column I in table B to, say, Fixed Decimal (25,18), you will see that the values generated are very slightly "off". This due to

1. Inability of decimal values to be represented exactly

2. Imprecision arising from numerical operations on imprecisely represented decimal values

For this reason some care must be taken if you intend to do row selection based on comparison of continuous numerical values.

You can make a new column called, say, Reference, as numeric, continuous, and enter by hand the values 0, 0.1, 0.2, ..., 0.9, 1. Then, if you make a new numeric, continuous column called Diff with a formula of :I-:Reference you will see that several rows are different from zero. So testing the difference reveals the issue.

If you change the formula to Round(:I-:Reference, 1) then you should see agreement. In particular this formula should select the row corresponding to 1.0 in your table:

Current Data Table() << Select Where(Round(I - 1.0, 1) == 0);

Corrected Round formula.

Highlighted

Re: Why can't I select certain numerical rows?

The odd thing is that I considered the possibility of a floating-point rounding error, but had the same result even when using fixed precision, for example:

Clear Globals();

New Table( "A", New Column( "I", Fixed Decimal (5,2), continuous ) );

aggregateColumn = {};

For( j = 1, j <= 5, j++,

allCurrents = {};

For( k = 0.0, k <= 6.0, k += 0.1, Insert Into(allCurrents, k); );

Column("I") << Set Values(allCurrents);

Insert Into(aggregateColumn, As List(Column("I") << Get Values));

);

aggTable = New Table( "B",

New Column( "I", Fixed Decimal (5,2), continuous, Set Values(aggregateColumn) )

);

In this case, when I increase the width and precision of column I in table B I see:

0.0000000000000000

0.1000000000000000

0.2000000000000000

0.3000000000000001

...

Apparently the numbers in the for loop are numerics, but shouldn't they be casted to fixed precision in the New Column() call?  This suggests that the cast from numeric -> fixed always uses the maximum possible precision behind the scenes.

Super User

Re: Why can't I select certain numerical rows?

Changing the format doesn't change how the value is stored, only how it is displayed.

Re: Why can't I select certain numerical rows?

Is it possible to cast the constants in the loop to fixed precision?

Community Trekker

Re: Why can't I select certain numerical rows?

ROUND the values before storing them.

Super User

Re: Why can't I select certain numerical rows?

Unfortunately that doesn't help here. E.g. Round(0.3, 1)  (round to 1 decimal place) is stored same as 0.3.in JMP.

Community Trekker

Re: Why can't I select certain numerical rows?

So?  Then if you test if the stored value matches the constant that you have typed into your code it will match.

Super User

Re: Why can't I select certain numerical rows?

No. It's a hardware issue. In base 10 numbers like 0.1 or 0.3 are exact values. However in the base that the computer uses, they are not exact.

Re: Why can't I select certain numerical rows?

This should be true for numerics but not fixed precision, unless fixed precision is just cosmetic.  In `fixed precision(2,1)`, 0.3 == 0.3 by definition.

I believe this question remains unanswered for the case of fixed precision.

Are the JMP data types actually documented anywhere?