58590_en-US

liliconcom

Delete rows based on 2 conditions

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

Thank you very much, it works perfectly.

Regards

Sam

Re: Delete rows based on 2 conditions

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

sorry code was cut by image

dt = current data table();

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

Re: Delete rows based on 2 conditions

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 conditionsHi Uday, I tried it did not work, my case conditions are dunamic
thanks
SamRe: Delete rows based on 2 conditions

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