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
seb38_cremer
Level I

Jmp : select where

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

3 REPLIES 3
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Jmp : select where

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

);


ron_horne
Super User (Alumni)

Re: Jmp : select where

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 )

     );

);

msharp
Super User (Alumni)

Re: Jmp : select where

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