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
- :
- Discussions
- :
- Check if multiple column values fall within range

Topic Options

- 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
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 22, 2015 10:27 AM
(3581 views)

Sample table:

Part | Test 1 | Test 2 | Test 3 | Test 4 |

A | 0.2 | 0.4 | 0.3 | 0.1 |

B | 0.2 | 0.3 | 0.4 | 0.6 |

C | 0.7 | 0.1 | 0.2 | 0.4 |

I would like to add a column to this table that evaluates every value in columns Test 1-Test 4, and if all fall within a range to output "Yes".

For example, if my range was 0 < values < 0.5, the output would look like this (I've highlighted failing values in red)

Part | Test 1 | Test 2 | Test 3 | Test 4 | Yes/No |

A | 0.2 | 0.4 | 0.3 | 0.1 | Yes |

B | 0.2 | 0.3 | 0.4 | 0.6 | No |

C | 0.7 | 0.1 | 0.2 | 0.4 | No |

Is there a simple way to achieve this with JSL?

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

hi hooshies,

to answer your exact question the following script can do it.

A more general solution depends on the aspect that needs generalizing (i.e. more columns, different ranges for different columns and whether you want it in a column formula or just to be calculated once by jsl)

dt = New Table**(** "Data source",

Add Rows**(** **3** **)**,

New Column**(** "Part", Character, Nominal, Set Values**(** **{**"A", "B", "C"**}** **)** **)**,

New Column**(** "Test 1", Set Values**(** **[****0.2**, **0.2**, **0.7]** **)** **)**,

New Column**(** "Test 2", Set Values**(** **[****0.4**, **0.3**, **0.1]** **)** **)**,

New Column**(** "Test 3", Set Values**(** **[****0.3**, **0.4**, **0.2]** **)** **)**,

New Column**(** "Test 4", Set Values**(** **[****0.1**, **0.6**, **0.4]** **)** **)**,

**)**;

dt << **New Column(** "Yes/No",

Character,

Nominal,

Formula**(**

If**(**

Maximum**(**

Abs**(** :Test 1 **)**,

Abs**(** :Test 2 **)**,

Abs**(** :Test 3 **)**,

Abs**(** :Test 4 **)**

**)** > **0.5**,

"No",

"Yes"

**)**

**)**

**)**;

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

ron_horne's method can be generalized. The key is to recognize that the Minimum() and Maximum() functions return the min/max value of their arguments. So, as you pose it, the comparison it can be written:

Minimum

(:Test 1, :Test 2, :Test 3, :Test 4)>0.23&Maximum

(:Test 1, :Test 2, :Test 3, :Test 4)<.5;

-Jeff

-Jeff

4 REPLIES

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

hi hooshies,

to answer your exact question the following script can do it.

A more general solution depends on the aspect that needs generalizing (i.e. more columns, different ranges for different columns and whether you want it in a column formula or just to be calculated once by jsl)

dt = New Table**(** "Data source",

Add Rows**(** **3** **)**,

New Column**(** "Part", Character, Nominal, Set Values**(** **{**"A", "B", "C"**}** **)** **)**,

New Column**(** "Test 1", Set Values**(** **[****0.2**, **0.2**, **0.7]** **)** **)**,

New Column**(** "Test 2", Set Values**(** **[****0.4**, **0.3**, **0.1]** **)** **)**,

New Column**(** "Test 3", Set Values**(** **[****0.3**, **0.4**, **0.2]** **)** **)**,

New Column**(** "Test 4", Set Values**(** **[****0.1**, **0.6**, **0.4]** **)** **)**,

**)**;

dt << **New Column(** "Yes/No",

Character,

Nominal,

Formula**(**

If**(**

Maximum**(**

Abs**(** :Test 1 **)**,

Abs**(** :Test 2 **)**,

Abs**(** :Test 3 **)**,

Abs**(** :Test 4 **)**

**)** > **0.5**,

"No",

"Yes"

**)**

**)**

**)**;

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

Re: Check if multiple column values fall within range

Ron,

Thanks for you response. The script you provided gets the job done in this situation, but I would like a more robust/general solution (for example, when the limits of my testing criteria are 0.23 < test values < 0.5).

Is there a way to set a column formula or script a solution where multiple columns are treated as an array? In pseudocode:

lower_limit = 0.23

upper_limit = 0.5

If lower_limit < [Column(2), Column(3), Column(4), Column(5)] < upper_limit

disp "Yes"

Else

disp "No"

The pseudocode here would test each value in columns 2-5 individually against the limits, and output "Yes" if all are in range.

Multiple AND/OR statements would achieve this, but seems like an elementary approach.

Thanks!

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

ron_horne's method can be generalized. The key is to recognize that the Minimum() and Maximum() functions return the min/max value of their arguments. So, as you pose it, the comparison it can be written:

Minimum

(:Test 1, :Test 2, :Test 3, :Test 4)>0.23&Maximum

(:Test 1, :Test 2, :Test 3, :Test 4)<.5;

-Jeff

-Jeff

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

Re: Check if multiple column values fall within range

New Column("In Range?", Formula(

If(

Min( :Test 1, :Test 2, :Test 3, :Test 4 ) > 0 &

Max( :Test 1, :Test 2, :Test 3, :Test 4 ) < 0.5,

"Yes",

"No"

)

)