Delete rows based on 2 conditions

[#1]

Hi, I have a data table "Table1", I need to have a JSL script that deletes rows based on both "TesName" & "Cpk" columns conditions.

First condition, column "TestName" will have repeated rows for example DOE for multi doses (same testname) that yields different Cpks; 2nd condition, I need to keep the row with lowest Cpk and delete the rest. Output of Table 1 is what you would expect from the script

Thanks in advance,

Sam

[#2]

@Anonymous, 
     The following example illustrates how you can apply it to your case. 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" ); 

Show(n Rows(dt)); 

dt << Select Where(:age == 12 & :height > 60); 

dt << Delete Rows; 

Show(n Rows(dt)); 

[#3]

Hi Uday, I tried it did not work, my case conditions are dunamic
thanks
Sam

[#4]

I got this script to get me closer but final table still missing other data from other columns, Split, Mean, Sigma, Min, Median, Max

dt = current data table();

dt << Summary( Group( :T

estName), Min( :Cpk ));

[#5]

sorry code was cut by image

dt = current data table();

dt << Summary( Group( :TestName), Min( :Cpk ));

[#6 · Accepted solution]

Try this

dt  = current data table();
dt << New Column( "MinCpk", numeric, formula(Col Minimum( :Cpk, :TestName )));
dt << select where(:Cpk > :MinCpk);
dt << delete rows;
dt << delete Column("MinCpk");

//___________________________________________________________
//or use this to keep the original table and create a new table with only the rows of interest 
dt = Current Data Table();
dt << New Column( "MinCpk", numeric, formula( Col Minimum( :Cpk, :TestName ) ) );
idx = dt << get rows where( :Cpk == :MinCpk );
newtbl = dt << subset( Rows( idx ), All Columns, Output Table Name( "Table2" ) );
newtbl << delete columns( "MinCpk" );

[#7]

Thank you very much, it works perfectly.

Regards

Sam