Search delimited column for values in list

pmroz

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

vince_faller

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