cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
hooshies
Level I

Check if multiple column values fall within range

Sample table:

PartTest 1Test 2Test 3Test 4
A0.20.40.30.1
B0.20.30.40.6
C0.70.10.20.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)

PartTest 1Test 2Test 3Test 4Yes/No
A0.20.40.30.1Yes
B0.20.30.40.6No
C0.70.10.20.4No

Is there a simple way to achieve this with JSL?

2 ACCEPTED SOLUTIONS

Accepted Solutions
ron_horne
Super User (Alumni)

Re: Check if multiple column values fall within range

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"

          )

     )

);

View solution in original post

Jeff_Perkinson
Community Manager Community Manager

Re: Check if multiple column values fall within range

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

View solution in original post

4 REPLIES 4
ron_horne
Super User (Alumni)

Re: Check if multiple column values fall within range

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"

          )

     )

);

hooshies
Level I

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!

Jeff_Perkinson
Community Manager Community Manager

Re: Check if multiple column values fall within range

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
vince_faller
Super User (Alumni)

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"

)

)

Vince Faller - Predictum