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
- :
- Discussions
- :
- Search delimited column for values in list

Topic Options

- Start Article
- 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
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 25, 2016 7:46 AM
(4196 views)

Hello JMP folks,

I often use **contains** to search a column for any values in a list. Something like this:

match_list = **{**"AAA", "CCC"**}**;

single_rows = dt << **get rows where(**contains**(**match_list, :Single Value**))**;

This is a very convenient way to search a column containing single values. However many times I need to do a similar search where the column contains multiple values separated by a delimiter (usually a semicolon).

My question: is there a slick way to search a column of delimited values in a similar fashion to the method outlined above? I can use regular expressions to search for a single value in the multi-value column. However I need to search the multi-value column for many values contained in a list.

Here's some code that generates the above table and demonstrates the contains method on a single value column.

dt = New Table**(** "Searchtbl", Add Rows**(** **6** **)**,

New Column**(** "Single Value", Character, Nominal,

Set Values**(** **{**"AAA", "BBB", "CCC", "DDD", "EEE", "FFF"**}** **) ****)**,

New Column**(** "Delimited Value", Character, Nominal,

Set Values**(** **{**"AAA", "AAA;BBB;CCC;DDD;EEE", "BBB;CCC;DDD", "BBB;DDD;EEE;AAA",

"BBB;EEE", "FFF;BBB"**}** **)**

**)**

**)**;

match_list = **{**"AAA", "CCC"**}**;

single_rows = dt << **get rows where(**contains**(**match_list, :Single Value**))**;

1 ACCEPTED SOLUTION

Accepted Solutions

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

I had to create a function to do it. but this appears to work

list_doge = function(//sees what items from list_1 are in list_2

{list_1, list_2}, {DEFAULT LOCAL},

aa1 = associative array(list_1);

aa2 = associative array(list_2);

aa1 << intersect(aa2);

aa1 << get keys;

);

dt<<Get Rows Where(nitems(list_doge(Words(:Delimited Value[row()], ";"), match_list)))

3 REPLIES 3

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

I had to create a function to do it. but this appears to work

list_doge = function(//sees what items from list_1 are in list_2

{list_1, list_2}, {DEFAULT LOCAL},

aa1 = associative array(list_1);

aa2 = associative array(list_2);

aa1 << intersect(aa2);

aa1 << get keys;

);

dt<<Get Rows Where(nitems(list_doge(Words(:Delimited Value[row()], ";"), match_list)))

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

Re: Search delimited column for values in list

Wow Vince that is fabulous. I tinkered with it and came up with a function that borrows heavily from this post by MS: Re: Quick way to compare two lists (uncommon elements)?

Here's some sample code. Turns out you don't need the [row()] construct. But why do you need nitems?

list_intersect = function**(**//sees what items from list_1 are in list_2

**{**list_1, list_2**}**,

**{**DEFAULT LOCAL**}**,

intersect_list = Associative Array**(** list_1 **)** << **intersect(** Associative Array**(** list_2 **)** **)** << **getkeys**;

**)**;

list1 = **{**"apple", "pear", "orange", "kiwi", "watermelon", "mango", "lemon", "bosc pear"**}**;

list2 = **{**"apple", "pear", "kiwi", "orange", "strawberry", "watermelon", "melon"**}**;

dt = New Table**(** "temp",

New Column**(** "Fruit", character, setvalues**(** list1 || list2 **)** **)**

**)**;

list1_rows = dt << **Get Rows Where(**nitems**(**list_intersect**(**Words**(**:Fruit, ";"**)**, list1**)))**;

list2_rows = dt << **Get Rows Where(**nitems**(**list_intersect**(**Words**(**:Fruit, ";"**)**, list2**)))**;

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

Re: Search delimited column for values in list

If you don't have the nitems, I think the <<get rows where() will look for a boolean and instead get a list. With the nitems, if there are no intersections, there will be nothing in the list, so false.