cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
matthewh
Level I

Select rows where column values match items in an arbitrary list

Hi,

I am trying to write some JSL code to select rows from a table where the value of a column matches some values, which are stored as a list. However, the list may have an arbitrary number of items in it.

I have tried the code at the bottom of the message (where b1 is the list containing my values to be selected, :Lot is the column to compare against and dt2 is the data table to be selected from) but that just selects the rows matching b1[last1]. I know I could produce a subset table for each value of b1[jj] and concatenate them afterwards, but that is messy and causes it's own problems later.

I also tried an Eval(Substitute(Expr( type structure but that didn't work.

Does anyone have any ideas? Or am I going about this completely the wrong way?

Cheers,

Matthew.

For( jj = 1, jj <= last1, jj++,
dt2 << Select Where( :Lot == b1[jj] )
);
1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Select Where

Hi Matthew,

Try the contains() function.

dt=open("$SAMPLE_DATA\Big Class.jmp");

ages={13, 14, 15};

dt<<select where(contains(ages, age));
-Jeff

View solution in original post

14 REPLIES 14
afterword
Level IV

Re: Select Where

Are you trying to select all the rows at once? as in

select all rows matching b1[1], b1[2]...,
perform operation

Or are you trying to match the list sequentially and then perform some operation on the matching cells?

select rows matching b1[1],
perform operation,
select rows matching b1[2],
perform operation,
...
matthewh
Level I

Re: Select Where

I'm trying to select all rows that match the list then perform an operation on those rows.

Re: Select Where

Hey did you ever figure out a solution to this? I am seeking a similar JSL for a data set and am unable to get the Select where function to select more than one condition. The current JSL I have is Data Table( Untitled ) << select where (:Name 2 == ABC OR DEF).

This doesnt work. It works for selecting the first condition, but no any subsequent ones. Any assistance or guidance will be appreciated. Thanks.

Message was edited by: DLW

Message was edited by: DLW
matthewh
Level I

Re: Select Where

Hi,

unfortunately I didn't manage to solve this! I'm hopeful one of the JMP gurus on the forum can help here.

Cheers,

Matthew.

Re: Select Where

If you are not attached to "Select Where", one can try:

For( jj = 1, jj <= N Items(b1), jj++,
For each row( If (:Lot == b1[jj],
Selected(RowState()) = 1)
));
Jeff_Perkinson
Community Manager Community Manager

Re: Select Where

Hi Matthew,

Try the contains() function.

dt=open("$SAMPLE_DATA\Big Class.jmp");

ages={13, 14, 15};

dt<<select where(contains(ages, age));
-Jeff

Re: Select Where

hi, Jeff

i hv tried, the script seems working to ur datatable but not mine.

could u pls help if column name : vendor, contain AABC, DAAF, GIAA
how to write the script to select where vendor name contain AA?

THanks!
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Select Where

Try this:

dt<<select where(contains(:vendor,"AA"));
matthewh
Level I

Re: Select Where

Thanks, that works well!