Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Delete rows based on 2 conditions

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 29, 2018 2:28 PM
(1229 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Delete rows based on 2 conditions

@Sam18,

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

Uday

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Delete rows based on 2 conditions

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

thanks

Sam

thanks

Sam

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Delete rows based on 2 conditions

sorry code was cut by image

dt = current data table();

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Delete rows based on 2 conditions

Thank you very much, it works perfectly.

Regards

Sam