Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Select rows where column values match items in an arbitrary list

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2009 7:48 AM
(13557 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Hi Matthew,

Try the contains() function.

```
dt=open("$SAMPLE_DATA\Big Class.jmp");
ages={13, 14, 15};
dt<<select where(contains(ages, age));
```

-Jeff

14 REPLIES 14

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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,

...

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,

...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select Where

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

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

Cheers,

Matthew.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Hi Matthew,

Try the contains() function.

```
dt=open("$SAMPLE_DATA\Big Class.jmp");
ages={13, 14, 15};
dt<<select where(contains(ages, age));
```

-Jeff

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select Where

Try this:

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Select Where

Thanks, that works well!