Select rows based upon multiple conditions

[#1]

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.

See Also

Select rows from the currently selected rows

Select rows that match any value in a list

[rishdude23]

@Wendy_Murphrey 

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"
	)
	)
)
)

[Wendy_Murphrey]

@rishdude23 

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.