Subscribe Bookmark RSS Feed

Search delimited column for values in list

pmroz

Super User

Joined:

Jun 23, 2011

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

11690_Search Table.png

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
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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

pmroz

Super User

Joined:

Jun 23, 2011

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

vince_faller

Super User

Joined:

Mar 17, 2015

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.