Conditional Select Where

[#1]

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

[#2]

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

);

[#3]

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

[#4]

@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

[#5 · Accepted solution]

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

);