topic Re: How to use Select Where () with Contains () to find partial matches in data table? in Discussions

How to use Select Where () with Contains () to find partial matches in data table?

Neo — Mon, 18 Dec 2023 17:55:07 GMT

I have a list

myList  = {"Current_I20_", "Voltage_CIB_", "Capacitance_DIFF_", "Resistance_FURR_"};

and a data table with one of columns called Parameter with some row entries as

Current_I20_Stage2
Voltage_CIB_Stage3
Capacitance_DIFF_corner_Stage1
Resistance_FURR__side_Stage3

Using myList with Contains() to extract the full parameters names from the data table into a list. How to do this via JSL?

For a start, the following is not selecting anything in my data table. Where am I going wrong?

dt<< select where(Contains(mylist, As column (Parameter)));

Re: How to use Select Where () with Contains () to find partial matches in data table?

Mark_Bailey — Mon, 18 Dec 2023 17:34:51 GMT

Try adding empty square brackets after the closing parenthesis of your function call.

dt<< select where(Contains(mylist, As column (Parameter)[]));

Re: How to use Select Where () with Contains () to find partial matches in data table?

Neo — Wed, 03 Jan 2024 13:47:21 GMT

@Mark_Bailey Done, I do not get any errors but it does not select anything in the data table.

Re: How to use Select Where () with Contains () to find partial matches in data table?

jthi — Mon, 18 Dec 2023 17:58:43 GMT

You need full matches when using contains with a list. You could use Word() to make your data table values be in same format as your list and then use contains (also convert the list to list of strings)

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("param",
		Character,
		"Nominal",
		Set Values(
			{"Current_I20_Stage2", "Voltage_CIB_Stage3", "Capacitance_DIFF_corner_Stage1",
			"Resistance_FURR__side_Stage3", "a", "b"}
		)
	)
);

my_list = {"Current_I20_", "Voltage_CIB_", "Capacitance_DIFF_", "Resistance_FURR_"};

rows = dt << Get Rows Where(Contains(my_list, Word([1 2], :param, "_") || "_"));
vals = dt[rows, "param"];

Re: How to use Select Where () with Contains () to find partial matches in data table?

pmroz — Mon, 18 Dec 2023 20:07:13 GMT

Here's what I do when looking for partial matches.

dt = New Table( "TestRegex", Add Rows( 8 ),
	New Column( "TestCol", Character, "Nominal",
		Set Values(
			{"Current_I20_Stage2", "xyz", "Capacitance_DIFF_corner_Stage1", "lmnop",
			"aaa", "Voltage_CIB_Stage3", "Voltage_CIB_Stage3",
			"Resistance_FURR__side_Stage3"}
		)
	)
);

myList  = {"Current_I20_", "Voltage_CIB_", "Capacitance_DIFF_", "Resistance_FURR_"};
for (i = 1, i <= nitems(mylist), i++,
	match_rows = dt << get rows where(! is missing(regex(as column(dt, "TestCol"), 
				mylist[i], "\0", ignorecase)));
	print(match_rows);
);

Re: How to use Select Where () with Contains () to find partial matches in data table?

Neo — Tue, 19 Dec 2023 13:00:35 GMT

@jthi This works but does not seem to honour hidden and excluded rows in the data table dt.

I want to exclude some rows based on another column in my data table as in JSL below. Perhaps this could be done within Get Rows Where ()?

Names Default To Here(1);
clear log ();
dt = New Table("Untitled 2",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("param",
		Character,
		"Nominal",
		Set Values(
			{"Current_I20_Stage2", "Voltage_CIB_Stage3", "Capacitance_DIFF_corner_Stage1",
			"Resistance_FURR__side_Stage3", "a", "b"}
		)
	),
	New Column("Group",
		Character,
		"Nominal",
		Set Values(
			{"A", "B", "C",	"D", "E", "F"}
		)
	),
);

ToHideAndExcludeList   =  {"A", "D", "F"}; 
dt << Select Where( Contains(ToHideAndExcludeList, :Group) );
sr = dt << Invert Row Selection;
sr << Hide and Exclude;

my_list = {"Current_I20_", "Voltage_CIB_", "Capacitance_DIFF_", "Resistance_FURR_"};

rows = dt << Get Rows Where(Contains(my_list, Word([1 2], :param, "_") || "_"));
vals = dt[rows, "param"];

Re: How to use Select Where () with Contains () to find partial matches in data table?

Mark_Bailey — Tue, 19 Dec 2023 14:20:46 GMT

You could include the Row State as another condition.

Re: How to use Select Where () with Contains () to find partial matches in data table?

Neo — Tue, 19 Dec 2023 15:36:26 GMT

@Mark_Bailey Thanks. Do you mean use Row State with Contains? Could I please have an example?

Re: How to use Select Where () with Contains () to find partial matches in data table?

mmarchandTSI — Tue, 19 Dec 2023 15:52:43 GMT

This should work.

Names Default To Here( 1 );
Clear Log();
dt = New Table( "Untitled 2",
	Add Rows( 6 ),
	Compress File When Saved( 1 ),
	New Column( "param",
		Character,
		"Nominal",
		Set Values( {"Current_I20_Stage2", "Voltage_CIB_Stage3", "Capacitance_DIFF_corner_Stage1", "Resistance_FURR__side_Stage3", "a", "b"} )
	),
	New Column( "Group", Character, "Nominal", Set Values( {"A", "B", "C", "D", "E", "F"} ) ),

);
ToHideAndExcludeList = {"A", "D", "F"};
dt << Select Where( Contains( ToHideAndExcludeList, :Group ) );
sr = dt << Invert Row Selection;
sr << Hide and Exclude;
my_list = {"Current_I20_", "Voltage_CIB_", "Capacitance_DIFF_", "Resistance_FURR_"};
exrows = dt << Get Excluded Rows;
rows = dt << Get Rows Where( All( Contains( my_list, Word( [1 2], :param, "_" ) || "_" ), Contains( exrows, Row() ) == 0 ) );
vals = dt[rows, "param"];

Re: How to use Select Where () with Contains () to find partial matches in data table?

Neo — Tue, 19 Dec 2023 16:20:54 GMT

@mmarchandTSI Many thanks. This works, but I would have preferred an approach where one could filter by row state ( as @Mark_Bailey suggested) under Contains (). This would avoid any data table manipulation.

Re: How to use Select Where () with Contains () to find partial matches in data table?

jthi — Tue, 19 Dec 2023 16:41:41 GMT

You could use !Excluded() and !Hidden() but Contains() seems to be doing some funny stuff in this case, so you have to manage around that, All() seems to work in this case

Names Default To Here(1);
Clear Log();
dt = New Table("Untitled 2",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("param",
		Character,
		"Nominal",
		Set Values(
			{"Current_I20_Stage2", "Voltage_CIB_Stage3", "Capacitance_DIFF_corner_Stage1",
			"Resistance_FURR__side_Stage3", "a", "b"}
		)
	),
	New Column("Group", Character, "Nominal", Set Values({"A", "B", "C", "D", "E", "F"})),

);

ToHideAndExcludeList = {"A", "D", "F"};
dt << Select Where(Contains(ToHideAndExcludeList, :Group));
sr = dt << Invert Row Selection;
sr << Hide and Exclude;
dt << Clear Select;

my_list = {"Current_I20_", "Voltage_CIB_", "Capacitance_DIFF_", "Resistance_FURR_"};

rows = dt << Get Rows Where(All(Contains(my_list, Word([1 2], :param, "_") || "_")) & !Excluded() & !Hidden());
vals = dt[rows, "param"];

Edit:

Or some eval expr

rows = dt << Get Rows Where(Eval(EvalExpr((Contains(my_list, Expr(Word([1 2], :param, "_") || "_"))))) & !Excluded() & !Hidden());

Re: How to use Select Where () with Contains () to find partial matches in data table?

Mark_Bailey — Tue, 19 Dec 2023 17:47:29 GMT

Yes, combine multiple conditions with And() to match strings for rows that are not currently hidden or excluded. There is a complete explanation and plenty of examples here.