cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

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

Table1 & Table2.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
gzmorgan0
Super User (Alumni)

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

View solution in original post

6 REPLIES 6
uday_guntupalli
Level VIII

Re: 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)); 
Best
Uday

Re: Delete rows based on 2 conditions

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

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

Untitled.jpgestName), Min( :Cpk ));

Re: Delete rows based on 2 conditions

sorry code was cut by image

dt = current data table();

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

gzmorgan0
Super User (Alumni)

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

Thank you very much, it works perfectly.

Regards

Sam