Subscribe Bookmark RSS Feed

Select Where: need to select from current selection in JSL

katief

Staff

Joined:

Oct 21, 2011

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

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
9 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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

Joined:

Oct 21, 2011

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

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

Joined:

Oct 21, 2011

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

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

David_Burnham

Super User

Joined:

Jul 13, 2011

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

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

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

Wendy_Murphrey

Joined:

Jun 23, 2011


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

Wendy