Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Why can't I select certain numerical rows?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Why can't I select certain numerical rows?

Apr 4, 2014 8:45 AM
(2990 views)

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

ROUND the values before storing them.

Highlighted
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?