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

Select Where: need to select from current selection in JSL

katief — Wed, 07 Dec 2011 21:39:08 GMT

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)

Select Where: need to select from current selection in JSL

ms — Wed, 07 Dec 2011 23:57:46 GMT

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 )

)

);

Select Where: need to select from current selection in JSL

katief — Thu, 08 Dec 2011 13:50:35 GMT

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.

Select Where: need to select from current selection in JSL

pmroz — Thu, 08 Dec 2011 15:59:59 GMT

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

Select Where: need to select from current selection in JSL

katief — Thu, 08 Dec 2011 17:16:40 GMT

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.

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

msharp — Wed, 29 Jul 2015 22:28:29 GMT

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

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

David_Burnham — Thu, 30 Jul 2015 10:37:10 GMT

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

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

Wendy_Murphrey — Thu, 30 Jul 2015 12:59:28 GMT

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

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

msharp — Fri, 04 Sep 2015 15:15:15 GMT

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

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

Wendy_Murphrey — Tue, 08 Sep 2015 20:47:15 GMT


To select a row without deselecting a previously selected row, combine << Select Where with

<< Select Where and the current selection("extend") argument. This is an alternative to

using an OR statement.

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

dt << Select Where( sex == "F", current selection("extend"));

msharp,

I am not sure where the syntax you show came from. The JMP 12.1 Scripting Guide and current online documentation do have the correct text, as shown above.

Thanks.

Wendy

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

doc4child — Mon, 22 Jan 2018 17:59:53 GMT

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;