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
rijking
Level II

Conditional Select Where

I have the below JSL code to select and subset data where there are fewer than 25 rows per 'tag', save the subset, and remove the subset from the main working dataset. This works fine where there are tags with fewer than 25 rows, but I need it to be robust and work where there are no tags with fewer than 25 rows - in which case it should ignore lines 4-6 and continue to line 7 to save the main working dataset with a new name. How can I achieve this?

Many thanks

1 Open( "data.jmp" );

2 dt=current data table() << Summary( Group( :tag ) );

3 Data Table( "data By (tag)" ) << Select Where( :N Rows < 25) << Sort( By( :N Rows ), Order( Ascending ), Output Table Name("summary_of_small_n"));

4 Data Table( "data" ) << Subset(( Selected Rows ), Output Table Name("sub_small_n"));

5 Data Table( "sub_small_n" ) << save("sub_small_n.jmp");

6 Data Table( "summary_of_small_n" ) << save("summary_of_small_n.jmp");

7 Data Table( "data" ) << delete rows << save("data_less_small_n.jmp");

1 ACCEPTED SOLUTION

Accepted Solutions
rijking
Level II

Re: Conditional Select Where

After a bit more fiddling, the below seems to work for me. Thanks for the help.

Open( "data.jmp" );

dt = current data table() << Summary( Group( :tag ) );

Data Table( "data By (tag)" ) << Select Where( :N Rows < 25) << Sort( By( :N Rows ), Order( Ascending ), Output Table Name("summary_of_small_n_tag"));

Data Table( "summary_of_small_n_tag" ) << save("summary_of_small_n_tag.jmp");

num_selected = nrows(dt << get selected rows);

if(num_selected(dt > 0),

// If the condition is satisfied, do this...

Data Table( "data" ) << Subset(( Selected Rows ), Output Table Name("sub_small_n"));

Data Table( "sub_small_n" ) << save("sub_small_n.jmp");

Data Table( "data" ) << delete rows << save("data_less_small_n.jmp");

,

// Else do this...

Data Table( "data" ) << save("data_less_small_n.jmp");

);

View solution in original post

4 REPLIES 4
ian_jmp
Staff

Re: Conditional Select Where

You need to use the 'NRow()' function in combination with an 'If()' expression. Something like:

Open( "data.jmp" );

dt = current data table() << Summary( Group( :tag ) );

dt2 = Data Table( "data By (tag)" ) << Select Where( :N Rows < 25) << Sort( By( :N Rows ), Order( Ascending ), Output Table Name("summary_of_small_n"));

if(

NRow(dt2 > 24,

// If the condition is satisfied, do this . . .

Data Table( "data" ) << Subset(( Selected Rows ), Output Table Name("sub_small_n"));

Data Table( "sub_small_n" ) << save("sub_small_n.jmp");

Data Table( "summary_of_small_n" ) << save("summary_of_small_n.jmp")

,

// Else do this . . .

Data Table( "data" ) << delete rows << save("data_less_small_n.jmp");

);


rijking
Level II

Re: Conditional Select Where

Ian, thanks. This doesn't quite work as:

a) I need to refer to the number of selected rows (rather than total rows that I understand NRow works on) - because dt2 summarizes the total number of rows for all tags, not just those < 25.

b) If referring to selected rows in dt2, I think this needs to be > 0 rather than > 24 - because dt2 summarizes the total number of rows for all tags, so I am interested in all selected rows (even if there is only one; that one would be summarizing a tag appearing on fewer than 25 rows in the underlying dt).

Any other suggestions would be much appreciated. Thank you.

Ian@JMP wrote:

You need to use the 'NRow()' function in combination with an 'If()' expression. Something like:

Open( "data.jmp" );

dt = current data table() << Summary( Group( :tag ) );

dt2 = Data Table( "data By (tag)" ) << Select Where( :N Rows < 25) << Sort( By( :N Rows ), Order( Ascending ), Output Table Name("summary_of_small_n"));

if(

NRow(dt2 > 24,

// If the condition is satisfied, do this . . .

Data Table( "data" ) << Subset(( Selected Rows ), Output Table Name("sub_small_n"));

Data Table( "sub_small_n" ) << save("sub_small_n.jmp");

Data Table( "summary_of_small_n" ) << save("summary_of_small_n.jmp")

,

// Else do this . . .

Data Table( "data" ) << delete rows << save("data_less_small_n.jmp");

);


uday_guntupalli
Level VIII

Re: Conditional Select Where

@rijking

            Just based on point (a) - would maybe trying an invert selection work? Have you tried it ?  If it doesn't work - can you please suggest one sample data table from the Sample Data Library which fits your problem so that I can try to reproduce the issue to try and help ?

Best
Uday

Best
Uday
rijking
Level II

Re: Conditional Select Where

After a bit more fiddling, the below seems to work for me. Thanks for the help.

Open( "data.jmp" );

dt = current data table() << Summary( Group( :tag ) );

Data Table( "data By (tag)" ) << Select Where( :N Rows < 25) << Sort( By( :N Rows ), Order( Ascending ), Output Table Name("summary_of_small_n_tag"));

Data Table( "summary_of_small_n_tag" ) << save("summary_of_small_n_tag.jmp");

num_selected = nrows(dt << get selected rows);

if(num_selected(dt > 0),

// If the condition is satisfied, do this...

Data Table( "data" ) << Subset(( Selected Rows ), Output Table Name("sub_small_n"));

Data Table( "sub_small_n" ) << save("sub_small_n.jmp");

Data Table( "data" ) << delete rows << save("data_less_small_n.jmp");

,

// Else do this...

Data Table( "data" ) << save("data_less_small_n.jmp");

);