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

[#3]

Hi Jim, 

Thanks for your reply,

I am very sorry, I realize that my request wasn’t very clear.

After prioritizing the group of rows within each Metric (which works well with your script),

I need to select the row of the highest value (Column :metric1), Not based on Max( theRows ) like in the current script.

The dummy table I provided before added to the confusion because "Col Max (:metric1)" within each group is always equal to "Max (theRows)".

Your suggested script doesn't work anymore with a new set of values. Below I replaced :metric1 with :Value to remove the Column name  ambiguity, plus I re-shuffled the numerical values.

I tried to play around with "Col Max ()" added as a rule inside the "get rows where()", but wasn’t successful.

(I realize my English is not perfect, so I added an image to describe my goal, if my wording isn't precise enough)

Do you have a suggestion?

Thanks again

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", "FLAG", "OK", "FLAG", "OK", "OK", "OK", "OK", "OK",
			"OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK"}
		)
	),
	New Column( "Value",
		Numeric,
		"Continuous",
		Format( "Best", 10 ),
		Set Values( [1, 2, 9, 5, 13, 2, 7, 9, 4, 11, 6, 8, 3, 1, 2, 13, 5, 5, 9, 12, 1, 2, 4, 5, 5, 9, 7] )
	)
);

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

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

dt << select rows( rowsToSelect );

[#4]

Can be done without extra columns but it might be easier with them

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

typecol = dt << New Column("ROWTYPE", Numeric, Continuous, Formula(
	If(:Flag1 == "FLAG" & :Flag2 == "FLAG",
		2
	, :Flag1 == "OK" & :Flag2 == "OK",
		0
	,
		1
	);	
));

dt << Select Where(
	:metric1 == Col Max(
		If(:ROWTYPE == Col Max(:ROWTYPE, :Metric),
			:metric1
		, 
			.
		),
		:Metric
	);
);

dt << Delete Columns(typecol);
wait(0);

One issue with this solution is that it might break in any upcoming JMP version as you aren't supposed to use anything else than column references with Col statistical functions. Here is a wish list item in hopes that JMP won't break one of the most useful "features" those functions currently have
Make using formula expressions in Set Each Value and using expressions as first argument in Col stat... (I'm trying to use those functions less and less but it is difficult as they are currently so powerful and they do create easier to faster easier to read scripts than other options).

[#5]

Jarmo,

Thanks a lot,

The solution you propose is working in my main script!

Although you mentioned that a future version of JMP might break this Col function arrangement.

We are always upgrading the JMP version to the latest release every year at my company.

I will use your script for now and will try to get Jim's help (txnelson) to make his suggestion work so my code will be more robust.

Thanks again!!

Voiz

[#6]

We don't know if JMP will change behavior. JMP considers it unintended behavior which means that it is subject to change at any point without any information. Give kudos to the wish list item, if you haven't yet, to change this into a functionality rather than unintended behavior ( Make using formula expressions in Set Each Value and using expressions as first argument in Col stat... )

[#7]

I just did, 

Thanks