News
On June 1, we’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
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?

0 Kudos
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
ron_horne
Super User

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

0 Kudos
Highlighted
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
Highlighted
ron_horne
Super User

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

0 Kudos
Highlighted
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!

0 Kudos
Highlighted
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

Highlighted
vince_faller
Super User

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