cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Select rows based upon multiple conditions

Problem

Suppose you wish to select rows in a data table that meet more than one condition. Or, maybe you want to select the rows that meet one of a few conditions.

Solution

The following examples demonstrate how you can use the logical AND (&) and OR (|) operators with the Select Where message to select rows that meet multiple conditions.  See the embedded comments for details.

``````/* Open a sample data table */
dt = Open( "\$SAMPLE_DATA\Big Class.jmp" );

/* Select rows where students are over 13, who are also less than 65 inches tall */
dt << Select Where( :age > 13 & :height < 65 );

Wait( 2 );           // For demonstration purposes

/* Select rows where students are either 12 or 14 years old */
dt << Select Where( :age == 12 | :age == 14 );``````

Discussion

Many more examples of using Select Where() can be found in the Select Rows section of the JMP Scripting Guide.

Select rows from the currently selected rows

Select rows that match any value in a list

Hi,

I am trying to write a New Column to the Big Data jmp file called "AgeSex_comp" with a formula containing a for long and a few conditions. I would like to compare the previous row's age and gender information with the next row to see whether they are the same or different. Here is the Script that I wrote, it does not work. Any ideas where I am going wrong?

``````dt  = current data table();

dt << NewColumn("AgeSex_Comp", Character, Nominal, Formula(
FOR(i=1, i<= N Row(dt), i++,
if((:age[Row(i)] == :age[Row(i+1)]) & (:sex[Row(i)] == :sex[Row(i+1)]),"Same age, same gender",
(:age[Row(i)] != :age[Row(i+1)]) & (:sex[Row(i)] == :sex[Row(i+1)]),"Diff age, same gender",
(:age[Row(i)] == :age[Row(i+1)]) & (:sex[Row(i)] != :sex[Row(i+1)]),"Same age, diff gender",
(:age[Row(i)] != :age[Row(i+1)]) & (:sex[Row(i)] != :sex[Row(i+1)]),"Diff age, diff gender"
)
)
)
)``````

A column formula natively iterates over the rows.  Therefore, the For() loop is unnecessary.  The Lag() function will return the value of the argument in the previous row.  So something like this might work for you.

``````	Formula(
If(
(:age == Lag( :age, 1 )) & (:sex == Lag( :sex, 1 )), "Same age, same gender",
(:age != Lag( :age, 1 )) & (:sex == Lag( :sex, 1 )), "Diff age, same gender",
(:age == Lag( :age, 1 )) & (:sex != Lag( :sex, 1 )), "Same age, diff gender",
(:age != Lag( :age, 1 )) & (:sex != Lag( :sex, 1 )), "Diff age, diff gender"
)
)
``````

Hope that helps.

This question is not related to using Select Where() but rather a column formula.  Questions such as this are better directed to the general Discussions area where many scripters can respond.

JSL Cookbook

If you’re looking for a code snippet or design pattern that performs a common task for your JSL project, the JSL Cookbook is for you.

This knowledge base contains building blocks of JSL code that you can use to reduce the amount of coding you have to do yourself.

It's also a great place to learn from the experts how to use JSL in new ways, with best practices.