topic Jmp : select where in Discussions

Jmp : select where

seb38_cremer — Thu, 26 Nov 2015 21:33:18 GMT

Hello I would try to generate new table with select where in a script and merge the subset table with another one

I succeed to generate a successful script except when the Select Where doesn't find row with the criteria ?

do you know how I can skip the join table part in my script if select where return nothing

regards

sebastien

Re: Jmp : select where

ms — Fri, 27 Nov 2015 00:21:16 GMT

There are probably several ways to do this. Here's an idea that should work if Select Where() have been applied:

If(N Row(dt << Get Rows Where(Selected())) > 0,

dtsub << join(...)

);

Another idea without Select Where() that makes a subset only if any rows match the criteria (compare with "F" in place of "Q"):

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

r = dt << get rows where(:sex == "Q");

If(N Row(r),

(dt << subset(rows(r))) << join(With(dt))

);

Re: Jmp : select where

ron_horne — Sun, 29 Nov 2015 22:11:32 GMT

hi seb38.cremer,

MS gave the right answer. yet, if it was not explicit enough thy the following:

Names Default To Here( 1 );

// open data table and make some changes

dt = Open( "$SAMPLE_DATA/Big Class.jmp" ) <<

set name( "table" ) <<

New Column( "Weight/Height", formula( :weight / :height ) );

// open another data table

dt2 = Open( "$SAMPLE_DATA/Big Class.jmp" );

// instead of using select use get rows where - it is more robust

choosen = dt2 << get rows where( :age == 12 );

// see if any were chosen. if yes subset and join

If( N Row( choosen ) > 0,

dt3 = dt2 << subset(

Rows( choosen ),

selected columns( 0 ) // brings all columns

);

// now join

dt3 << Join(

With( data table ("table") ),

By Matching Columns( :name = :name ),

Drop multiples( 0, 0 ),

Name( "Include non-matches" )(0, 0),

Preserve main table order( 1 )

);

Re: Jmp : select where

msharp — Mon, 30 Nov 2015 18:14:54 GMT

Two more examples using If(!isempty and my preferred try( method examples:

Names Default To Here( 1 );

// open data table and make some changes

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

dt << set name( "table" ) <<

New Column( "Weight/Height", formula( :weight / :height ) );

// open another data table

dt2 = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt2 << Select where(:sex == "Q");

dt3 = dt2 << Subset(

Selected Rows( 1 ),

Selected columns only( 0 )

);

If(!isempty(dt3), //if selected rows == 0 dt3 is an empty variable

dt4 = dt << Join(

With( dt3 ),

By Matching Columns( :name = :name ),

Drop multiples( 0, 0 ),

Include Nonmatches( 0, 0 ),

Preserve main table order( 1 )), //no table print error log

print("Empty subset table")

);

try(dt4 = dt << Join(

With( dt3 ),

By Matching Columns( :name = :name ),

Drop multiples( 0, 0 ),

Include Nonmatches( 0, 0 ),

Preserve main table order( 1 ))

, //catch expresion

Print("Join Failed"));