nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Neo

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

Accepted answers

mmarchandTSI

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"];

All comments

Mark_Bailey

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

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

Neo

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

jthi

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"];

pmroz

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

Neo

@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"];

Mark_Bailey

You could include the Row State as another condition.

Neo

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

jthi

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