[JSL] Select rows using "Complex" conditions

[#1]

Hello All,

I would like to write a JSL script to select the rows in a table with “complex” conditions:

I need to select ONE ROW per Metric (Column :Metric) with conditions IN ORDER OF PRIORITY:

1- if the condition Flag1 = “FLAG” AND Flag2 = “FLAG” exists, Select Col Max (:Metric1) of this condition

2- if the condition Flag1 = “FLAG” XOR Flag2 = “FLAG” exists, Select Col Max (:Metric1) of this condition

3- Else, Select row Max (Metric1)

Below a tentative code using For Each Row () and a debug column, but my attempt doesn’t work and is not very elegant.

Also, I am not sure how to do an XOR (exclusive OR) in JLS without using combination of basic gates.

Is there a better way to do this?

Note: please find attached a PNG that illustrate my request

Thanks

-Voiz

Names Default To Here(1);

dt = New Table("RawData",
	Add Rows(27),
	Compress File When Saved(1),
	New Column("Metric",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "C", "C", "C", "C", "C", "C", "C", "D", "D", "D", "D", "D", "D", "D"}
		)
	),
	New Column("Flag1",
		Character,
		"Nominal",
		Set Values(
			{"FLAG", "OK", "FLAG", "OK", "OK", "OK", "OK", "FLAG", "FLAG", "OK", "OK", "FLAG", "OK", "OK", "FLAG",
			"FLAG", "OK", "OK", "OK", "FLAG", "OK", "OK", "OK", "OK", "OK", "OK", "OK"}
		)
	),
	New Column("Flag2",
		Character,
		"Nominal",
		Set Values(
			{"OK", "OK", "OK", "OK", "OK", "FLAG", "OK", "FLAG", "FLAG", "OK", "OK", "FLAG", "OK", "OK", "OK",
			"OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK"}
		)
	),
	New Column("metric1",
		Numeric,
		"Continuous",
		Format("Best", 10),
		Set Values([1, 2, 3, 5, 7, 10, 13, 2, 4, 5, 6, 8, 11, 1, 2, 4, 5, 5, 9, 12, 1, 2, 4, 5, 5, 9, 18])
	)
);


//This attempt doesn't select the right rows

debugcol = dt << New Column("Debug", Numeric, Continuous);

For Each Row(dt, 

	FLAG_row = 
		If(
			:Flag1 == "FLAG" & :Flag2 == "FLAG",
				Col Max(:metric1, :Metric),
			IF(
				:Flag1 == "FLAG" | :Flag2 == "FLAG",
					Col Max(:metric1, :Metric),
				If(
					:Flag1 == "OK" & :Flag2 == "OK",
						Col Max(:metric1, :Metric)
			)));
	debugcol[Row()] = FLAG_row;
);

dt << select where (:metric1 == :Debug);

 

[#2]

Here is how I would do this

Names Default To Here( 1 );

dt = New Table( "RawData",
	Add Rows( 27 ),
	Compress File When Saved( 1 ),
	New Column( "Metric",
		Character,
		"Nominal",
		Set Values(
			{"A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "C", "C", "C", "C", "C", "C", "C", "D", "D",
			"D", "D", "D", "D", "D"}
		)
	),
	New Column( "Flag1",
		Character,
		"Nominal",
		Set Values(
			{"FLAG", "OK", "FLAG", "OK", "OK", "OK", "OK", "FLAG", "FLAG", "OK", "OK", "FLAG", "OK", "OK", "FLAG", "FLAG",
			"OK", "OK", "OK", "FLAG", "OK", "OK", "OK", "OK", "OK", "OK", "OK"}
		)
	),
	New Column( "Flag2",
		Character,
		"Nominal",
		Set Values(
			{"OK", "OK", "OK", "OK", "OK", "FLAG", "OK", "FLAG", "FLAG", "OK", "OK", "FLAG", "OK", "OK", "OK", "OK", "OK",
			"OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK"}
		)
	),
	New Column( "metric1",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Values( [1, 2, 3, 5, 7, 10, 13, 2, 4, 5, 6, 8, 11, 1, 2, 4, 5, 5, 9, 12, 1, 2, 4, 5, 5, 9, 12] )
	)
);

dt << clear select;
rowsToSelect = {};
groups = associative array(:metric<<get values) << get keys;

For Each( {group}, groups,
	theRows = dt << get rows where( :metric == group & :Flag1 == "FLAG" & :Flag2 == "FLAG" );
	If( N Rows( theRows ) > 0,
		Insert Into( rowsToSelect, Max( theRows ) ),
		theRows = dt << get rows where( :metric == group & (:Flag1 == "FLAG" | :Flag2 == "FLAG") );
		If( N Rows( theRows ) > 0,
			Insert Into( rowsToSelect, Max( theRows ) ),
			Insert Into( rowsToSelect, Max( dt << get rows where( :metric == group ) ) )
		);
	);
);

dt << select rows( rowsToSelect );