cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Choose Language Hide Translation Bar
Select rows that match any value in a list

## 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.

Select rows from the currently selected rows

Select rows based upon multiple conditions

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

is cool

is there something similar for

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

?

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

same result, yes.
but different syntax.

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)));``````

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",
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 );
);``````

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.