Search delimited column for values in list<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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));
Re: Search delimited column for values in listIf you don't have the nitems, I think the <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)));
Re: Search delimited column for values in listI 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)))
