BookmarkSubscribeRSS Feed
katief

Staff (Retired)

Joined:

Oct 21, 2011

Select Where: need to select from current selection in JSL

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Wendy_Murphrey

Joined:

Jun 23, 2011

Solution

Re: Select Where: need to select from current selection in JSL

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

Wendy
10 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Select Where: need to select from current selection in JSL

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 )

  )

);

katief

Staff (Retired)

Joined:

Oct 21, 2011

Select Where: need to select from current selection in JSL

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.

pmroz

Super User

Joined:

Jun 23, 2011

Select Where: need to select from current selection in JSL

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

katief

Staff (Retired)

Joined:

Oct 21, 2011

Select Where: need to select from current selection in JSL

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.

msharp

Super User

Joined:

Jul 28, 2015

Re: Select Where: need to select from current selection in JSL

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

doc4child

Senior Member

Joined:

Jan 22, 2018

Re: Select Where: need to select from current selection in JSL

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;
David_Burnham

Super User

Joined:

Jul 13, 2011

Re: Select Where: need to select from current selection in JSL

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

-Dave
Wendy_Murphrey

Joined:

Jun 23, 2011

Solution

Re: Select Where: need to select from current selection in JSL

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

Wendy
msharp

Super User

Joined:

Jul 28, 2015

Re: Select Where: need to select from current selection in JSL

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