Select rows that match any value in a list

[#1]

Problem

Suppose you prompt a user to make a selection. When you retrieve the values from the user, typically they are stored in a list.  Now you need to select the data that matches the values in the list.

Solution

The following example demonstrates how you can select rows in a data table that contain any value stored in a JSL list. Lists containing either numeric values or character strings can be used and are shown below.  See the embedded comments for details.

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

/* Create a list of ages. */
myList = {9, 12, 15, 17, 18};

/* Select all rows which have an age that is in the list. */
dt << Select Where( Contains( myList, :age ) );

Wait( 2 );           // For demonstration purposes

/* Create a list of student names. */
myStudents = {"HENRY", "ROBERT", "BARBARA", "SALLY", "MARION"};

/* Select all rows which have any of the names in the list. */
dt << Select Where( Contains( myStudents, :name ) );

Discussion

In these examples, the Contains() function is used to compare each row to the list of values.

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 based upon multiple conditions

[hogi]

dt << Select Where( Any( Row() == Index( 3, 5 ) ) );

is cool

is there something similar for

dt << Select Where( Any( :name == {"JANE", "DAVID","PATTY"}) );

?

[jthi]

Isn't that what you would use Contains() for?

[hogi]

same result, yes.
but different syntax.

[jthi]

Is there some specific need to use more complex syntax? You could use Loc with Any

Names Default To Here(1);

dt = Open("$SAMPLE_DATA\Big Class.jmp");
dt << Select Where(Any(Loc({"JANE", "DAVID", "PATTY"}, :name)));

[hogi]

Ah, nice

I feared that contains is slower. but actually ...
note to myself: use contains 

View more...

Names Default To Here( 1 );
Try(close(dt, noSave));
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

results = New Table( "Untitled",
	Add Rows( 0 ),
	New Column( "rows" ),
	New Column( "t_contains_string" ),
	New Column( "t_any_loc_string" ),
	New Column( "t_contains_number" ),
	New Column( "t_any_loc_number" ),
	New Column( "t_any_number" )
);

dt << New Column( "nameID", Formula( Row() ) );
dt:nameID << delete formula;

For( i = 1, i <= 20, i++,

	t0 = HP Time();
	dt << Select Where( Contains( {"JANE", "DAVID", "PATTY"}, :name ) );
	t1 = ((HP Time() - t0) / 1000000);

	t0 = HP Time();
	dt << Select Where( Any( Loc( {"JANE", "DAVID", "PATTY"}, :name ) ) );
	t2 = ((HP Time() - t0) / 1000000);

	t0 = HP Time();
	dt << Select Where( Contains( {3, 15, 20}, :nameID ) );
	t3 = ((HP Time() - t0) / 1000000);
	
	t0 = HP Time();
	dt << Select Where( Any( Loc( [3 15 20], :nameID ) ) );
	t4 = ((HP Time() - t0) / 1000000);

	t0 = HP Time();
	dt << Select Where( Any( :nameID == [3 15 20] ) );
	t5 = ((HP Time() - t0) / 1000000);

	results << add rows( {:rows = N Rows( dt ),:t_contains_string = t1, :t_any_loc_string = t2, :t_contains_number = t3, :t_any_loc_number = t4, :t_any_number = t5} );
	dt = dt << concatenate( {dt}, Append to first table );
);