Jmp : select where

[#1]

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

[#2]

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

);

[#3]

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 )

     );

);

[#4]

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