topic Re: Delete rows based on 2 conditions in Discussions

Delete rows based on 2 conditions

Anonymous — Tue, 29 May 2018 21:28:26 GMT

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

Re: Delete rows based on 2 conditions

uday_guntupalli — Tue, 29 May 2018 22:32:03 GMT

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

Re: Delete rows based on 2 conditions

Anonymous — Tue, 29 May 2018 23:46:30 GMT

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

Re: Delete rows based on 2 conditions

Anonymous — Tue, 29 May 2018 23:57:39 GMT

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

Re: Delete rows based on 2 conditions

Anonymous — Tue, 29 May 2018 23:59:06 GMT

sorry code was cut by image

dt = current data table();

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

Re: Delete rows based on 2 conditions

gzmorgan0 — Wed, 30 May 2018 09:55:14 GMT

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

Re: Delete rows based on 2 conditions

Anonymous — Wed, 30 May 2018 17:35:55 GMT

Thank you very much, it works perfectly.

Regards

Sam