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
CG
CG
Level III

how do i invert Unhide and unexclude rows based on values in few columns

Hello,

I need some guidance here....

I have two columns (per_Change) and Step (it's a formula based values) and Per_Change column is sorted from -ve to +ve values.

In my script i need to know max rows so i created rank column from 1-14 (ascending order as attached image) and need to have atleast 8 rows (starting with highest rank).

 

Steps:

1. Exclude -ve values in Per_Change column

2. then in Col Step is based on some formula, and it will create some "-ve" and "+ve" and some "null" values. Hide and exclude all -ve values in col Step. the null values were already hidden and excluded based on Col Per_Change since formula will generate -ve values.

3. My requirement is to have atleast 8 values (descending order). In my example there are 6 clear row states (rank 9-14)  (2 additional rows should be from descending i.e. unhide/unexclude rank 8 and 7 but should not include "null" values in a scenario if there are more null values and fewer -ve or +ve values in col Step).

 

Step 1 and 2, i am able to perform in JSL script but am having issues in enabling 2 additional rows.

I tried using lag function and also creating dummy columns to go enabling in reverse order but am having hard time for unselecting additional rows.

.Datatable.PNG

 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
gzmorgan0
Super User (Alumni)

Re: how do i invert Unhide and unexclude rows based on values in few columns

CG, I attached a data table, named PerChangeExample to emulate your example.

 

Try this script

Names Default to Here(1);

dt = Data Table("PerChangeExample");
dt << select where(:Per_change < 0 | IsMissing(:Per_change) );
dt << exclude(1) << hide(1);
dt << select where(:Step < 0 | IsMissing(:Step) );
dt << exclude(1) << hide(1);
dt << clear select;

nexcl = nrow( dt << get excluded rows() );
if ( nrow(dt) - nexcl < 8,
   for(i = nexcl, i > nrow(dt) -8 , i--,
      if ( !IsMissing(:Step[i]), 
            dt << Select Rows(i);
            dt << Exclude(0) << Hide(0);
            dt << clear select;
         )
    ); //end for i 
); // end if

nexcl = nrow( dt << get excluded rows() );
if ( nrow(dt) - nexcl < 8, throw() ); //stop program execution if still not enough data.

View solution in original post

2 REPLIES 2
gzmorgan0
Super User (Alumni)

Re: how do i invert Unhide and unexclude rows based on values in few columns

CG, I attached a data table, named PerChangeExample to emulate your example.

 

Try this script

Names Default to Here(1);

dt = Data Table("PerChangeExample");
dt << select where(:Per_change < 0 | IsMissing(:Per_change) );
dt << exclude(1) << hide(1);
dt << select where(:Step < 0 | IsMissing(:Step) );
dt << exclude(1) << hide(1);
dt << clear select;

nexcl = nrow( dt << get excluded rows() );
if ( nrow(dt) - nexcl < 8,
   for(i = nexcl, i > nrow(dt) -8 , i--,
      if ( !IsMissing(:Step[i]), 
            dt << Select Rows(i);
            dt << Exclude(0) << Hide(0);
            dt << clear select;
         )
    ); //end for i 
); // end if

nexcl = nrow( dt << get excluded rows() );
if ( nrow(dt) - nexcl < 8, throw() ); //stop program execution if still not enough data.
CG
CG
Level III

Re: how do i invert Unhide and unexclude rows based on values in few columns

Thank you for prompt reply...it works!