Select rows where column values match items in an arbitrary list

[#1]

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

[#2]

---

@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

[#3]

-> added to Tiny Traps in Jmp and JSL 

[#4]

Thanks, that works well!

[#5]

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

[#6]

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!

[#7]

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

[#8]

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

[#9]

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!