topic Re: Select Where in Discussions

Select rows where column values match items in an arbitrary list

matthewh — Thu, 02 Nov 2017 18:48:46 GMT

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

Re: Select Where

afterword — Thu, 19 Mar 2009 17:48:36 GMT

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

Re: Select Where

matthewh — Fri, 20 Mar 2009 09:06:57 GMT

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

Re: Select Where

Tue, 16 Jun 2009 06:38:23 GMT

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

Re: Select Where

matthewh — Tue, 16 Jun 2009 07:53:52 GMT

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

Wed, 04 Oct 2017 20:42:14 GMT

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

Re: Select Where

Jeff_Perkinson — Wed, 04 Oct 2017 21:03:58 GMT

Hi Matthew,

Try the contains() function.

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

ages={13, 14, 15};

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

Re: Select Where

matthewh — Wed, 17 Jun 2009 16:45:18 GMT

Thanks, that works well!

Re: Select Where

Fri, 19 Jun 2009 07:34:46 GMT

Hi,

Please advise my script:

Column Name : Vendor, Contains : ABC, DEF, GHI

1) Data Table( test ) << select where( :Vendor == ABC ) | (:Vendor == GHI)

2) Data Table( test ) << select where( :Vendor == ABC ) & (:Vendor == GHI)

Where I only get ABC not ABC & GHI.

Anyone can help I would like to get more than one vendor?

P.S:Please note Vendor, ABC, GHI in double quotation marks which can't show here.

Thanks!

Message was edited by: CLS

Re: Select Where

Fri, 19 Jun 2009 07:43:11 GMT

Hi, please advise my script:

1) Data Table( "test" ) << select where( :"Vendor" == "ABC" ) | (:"Vendor" == "GHI")
2) Data Table( "test" ) << select where( :"Vendor" == "ABC" ) & (:"Vendor" == "GHI")

Where I only get "ABC" not "ABC" & "GHI". Anyone can help to get more than one vendor?

Thanks!

Re: Select Where

ms — Wed, 04 Oct 2017 20:45:30 GMT

If I understand you correctly you wish to select both rows containing "ABC" as well as rows containing "GHI"?

You can not use the "And" operator since a cell can not have two different values simultaneously.

Use "Or" like in 1) but put paranthes around the whole logical statement, e.g.

Data Table( test ) << select where( Or(:Vendor == "ABC" , :Vendor == "GHI"))
//which equals
Data Table( test ) << select where( :Vendor == "ABC" | :Vendor == "GHI")

Re: Select Where

Mon, 22 Jun 2009 08:42:16 GMT

MS, Thank you very much, it works!!!

Re: Select Where

Wed, 04 Oct 2017 21:05:54 GMT

hi, how the contain function apply to my case where I want all Vendor Name contains "A" and "H"? This is for the circumstances where the vendor name not specific, (ABC Sdn Bhd, ABC Pte, ABC Subsidiary, GHI Sdn. Bhd., GHI Pte).

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

Thanks!

Re: Select Where

Tue, 23 Jun 2009 08:30:20 GMT

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!

Re: Select Where

ms — Wed, 04 Oct 2017 20:44:30 GMT

Try this:

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

Re: Select Where

hogi — Thu, 27 Jun 2024 19:25:04 GMT

Hi @Jeff_Perkinson , is there a similar trick which can be used together with the new Where?

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

ages={13, 14, 15};

dt<<select where(contains(ages, :age));

//how to make this work?
where(dt, contains(ages, :age));
where(dt, contains(ages, :age[]));
where(dt, contains(ages, As Column(:age)))

Re: Select Where

hogi — Fri, 08 Aug 2025 17:25:01 GMT

@hogi wrote:

Hi @Jeff_Perkinson , is there a similar trick which can be used together with the new Where?

unfortunately, there is no solution:TS-00225505
Lists, Matrices, and Columns are all assumed to vary together within Where. There is currently no way to escape/prevent this

Re: Select Where

hogi — Fri, 08 Aug 2025 17:28:32 GMT

-> added to