topic Re: hide and exclude rows base on multiple criteria in Discussions

How to hide and exclude rows based on multiple criteria?

Heidi — Wed, 22 Aug 2018 21:03:58 GMT

Hi,

I'm new to JSL. Here is my table. Under same ID and temp I want to exclude rows with higher current. I've been struggled with this and thanks in advance if anyone can help me out...

	ID	Temp	Current	Value
	C	25	35	1.14
	C	55	48	1.43
	C	70	60	1.64
	C	80	65	1.59
	D	25	35	1.18
	D	55	48	1.47
	D	70	60	1.68
	D	80	65	1.64
	E	25	35	1.2
	E	55	48	1.48
	E	70	60	1.65
	E	80	65	1.59
	F	25	35	1.13
	F	55	48	1.36
	F	70	60	1.53
Hide and Exclude	F	70	65	1.66
	F	80	65	1.41
Hide and Exclude	F	80	70	1.51

Re: hide and exclude rows base on multiple criteria

gzmorgan0 — Fri, 18 May 2018 06:01:19 GMT

I am not sure what criteria you want to use, but here is an example

Names Default to Here(1);

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

dt << select where(:sex=="F" &  14<=:age<16 & :weight>90);

dt << hide and exclude (1); //use 0 to unexclude and unhide

dt << clear select;

Re: hide and exclude rows base on multiple criteria

Heidi — Fri, 18 May 2018 11:48:06 GMT

Than you. The criteria that I need is not a fixed value. In the data table that I provided, if there are more than one rows with same ID and temp, I want to keep the rows with the lowest current and hide the rest rows with higher current.

do I need a for loop to make this?

Re: hide and exclude rows base on multiple criteria

Jeff_Perkinson — Fri, 18 May 2018 13:39:12 GMT

You can do this all interactively in the data table.

Start by sorting the data table by ID, Temp and Current.

Then, you can create a new column that looks for repeated ID, Temp combinations and sets an indicator.

Now you can right click on one of the 1's in the new column and choose Select Matching Values. That will select all the rows that have a 1 in that column. Now you can Hide and Exclude the rows.

Re: hide and exclude rows base on multiple criteria

O_Lippincott — Fri, 18 May 2018 14:10:26 GMT

In JMP 14, you can use the new "Select Duplicate Rows" function interactively or in JSL.

For example, in Big Class to select the weights that are higher under the same sex and height. First sort the table ascending by sex, height, and weight.

Highlight the sex and height columns in the datatable. Go to Rows>Row Selection>Select Duplicate Rows. This will select the everything but the first instance of the same sex and height. You can then hide and exclude from there by going to Rows>Hide and Exclude.

You can also script these steps.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Sort(replace table,
	By( :sex,:height,:weight),
	Order( Ascending )
);

dt << Select duplicate rows( Match( :sex, :height ));

dt << hide and exclude (1);

-Olivia