Select Where: need to select from current selection in JSL

[#1]

I would like to select rows from a data set where I have multiple conditions, but instead of using a single select where statement with "and," I want to loop through my conditions one at a time, selecting from the current selection. This is very straightforward in the select where window (use "select from current selection"). How can I do this in JSL?

For example, I have a list of column names cnames = {"Col1", "Col2", "Col3"} and a list of values cspecs = {"a", "b","c} and I want to select the rows from my data table where Col1 = a, Col2 = b, and Col3 = c. I will loop through the items in cnames and tell it to select from the current selection each time. Thanks!

dt<<

select where(as column(cnames)==cspecs)

[#2]

One could use a list of currently selected rows as part of the condition of Select Where(). The list can be updated with each iteration as in the example below.

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

cnames = {"age", "height", "weight"};

cspecs = {15, 62, 100};

dt << select all rows;

For( i = 1, i <= N Items( cnames ), i++,

          selected_rows = dt << get selected rows;

          dt << select where(

                    And(

                              As Column( cnames[i] )[] < cspecs[i],

                              Contains( selected_rows, Row() ) > 0 )

  )

);

[#3]

Thank you- this will help. I'd done a "for each row" to work around this and that killed my performance. Your suggestion will speed things up.

[#4]

It's probably more efficient to do the SELECT WHERE all at once.  I build dynamic select statements all the time and then use eval(parse()) to run them.  Here's MS's example modified to do the select where in one go.

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

cnames = {"age", "height", "weight"};

cspecs = {15, 62, 104};

select_string = "";

For (i = 1, i <= N Items(cnames), i++,

      if (select_string == "",

      // then

        select_string = ":" || cnames[i] || " == " || char(cspecs[i]);

      // else

        ,

        select_string = select_string || " & " ||

                        ":" || cnames[i] || " == " || char(cspecs[i]);

      );

);

select_string = "dt << select where(" || select_string || ")";

eval(parse(select_string));

print(select_string);

[#5]

Thank you!  Got a little tricky with character and numeric columns, but it's working and it's a big improvement speedwise from where I was.

[#6]

Been searching for a solution to pretty much the exact same issue, and this really is very slick.  Not sure why I didn't think of creating a text string to evaluate it before...

[#7]

I am trying to work this code that I wrote based on the script here. The Diagnosis columns are character columns and this code is not working. Basically there are three columns in the table, each one with ICD codes. I want to select the cases where it matches ICD codes. It should be any column matching any ICD code hence the nested for loop. The Uncommented code works, but Select string does not have quotation and probably that is why it is not working. How can I fix this?

dt = Open( "\Main.jmp" );
　
colnames = {"Diagnosis 1", "Diagnosis 2", "Diagnosis 3"};

ICDcodes = { "7731", "7626", "V3001", "V3000"};

tblname = "Subset.jmp";
/*
dt << Select Where(
:Diagnosis 1 == "V3001",
Current Selection( "extend" )
);*/

select_string = "";
 
For (i = 1, i <= N Items(colnames), i++,
　
For (j = 1, j <= N Items(ICDcodes), j++,

if (select_string == "",

// then

// select_string = ":" || colnames[i] || " == " || char(ICDcodes[j]);
select_string = ":" || colnames[i] || " == " || char(ICDcodes[j]) ;

// else

,
select_string = select_string || " | " ||

":" || colnames[i] || " == " || char(ICDcodes[j]);
 
);
　
);
);
　
select_string = "dt << select where(" || select_string || ")";
 
eval(parse(select_string));
 
print(select_string);
 
// Create a subset table with GA <32 weeks.
　
dt << Subset( Output Table( tblname ), Selected Rows( 1 ), selected columns( 0 ) );
 
dt2 = data table(tblname);
　
dt2 << save();

//dt << clear select;

[#8]

A For loop should be as fast if not faster then a where clause - but "For Each Row" is horribly slow - use For (i=1,i<=NRows(dt),,i++ instead

[#9 · Accepted solution]

JMP 12 offers a new option for Select Where() that allows you to specify whether to to extend, restrict, or clear the current selection. The syntax is as follows:

dt << Select Where( condition, <Current Selection( "extend" | "restrict" | "clear" )> );

The following example demonstrates extending the current selection.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Select Where( :Age == 14 );
Wait( 1 ); //For demonstration purposes
dt << Select Where(
  :sex == "M",
  Current Selection( "extend" )
);

Hope that helps!

Wendy

[#10]

Can we get this updated in the Scripting Guide?

//Scripting Guide page 336

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

dt << Select Where( age < 15 & sex == "F" );

//or

dt << Select Where( age == 14 );

dt << Extend Select Where( sex == "F" ); //does nothing

wait(10);

dt << Select Where( sex == "F", Current Selection ("extend")); //extends select