cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
tom_abramov
Level V

Querying jmp data table with JSL

Hi,

I need to get a list of distinct names for ages 12 and 13 in Big Class.jmp

My current way seems to be very inefficient. (Select where, subset...)

What is the way to do this with Query();

Thanks.

 

My script is:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
AgeList = {13, 15};
dt << Select where( Contains( AgeList, :Age ) > 0 );
SubsetDt = dt << Subset( Selected Rows, Columns( "name" ) );
Summarize( SubsetDt, DistinctNames = By( :(1) ) );
Close( SubsetDt, nosave );
Show( DistinctNames );
1 ACCEPTED SOLUTION

Accepted Solutions
gzmorgan0
Super User (Alumni)

Re: Querying jmp data table with JSL

Thank you, Jim, @txnelson .

 

@tom_abramov,  there are multple ways to do this. Since you used the word querying, I suspected you might have a more complex SQL type select so I used the JMP query()  function.  Below is a simple method not requiring SQL syntax. An associative array s a keyed list, and the keys must be unique.  :name[idx] produces a list of all rows with 12, 13 and 15 year olds. The associative array only has unique names with an associated value of 1. The unique names are the keys of the associative array. 

 

Names Default To Here( 1 );

dt = Open( "$Sample_Data/Big Class.jmp", invisible );

AgeList = {12, 13, 15};

idx = dt << get rows where(Contains(Agelist,:age));
uniq_names = Associative Array( :name[idx] ) <<get keys;
show(uniq_names);

 

View solution in original post

5 REPLIES 5
gzmorgan0
Super User (Alumni)

Re: Querying jmp data table with JSL

@tom_abramov ,

 

You did not mention which version of JMP that you are using.  I do not recall if the JMP query() function first was available in JMP 12 or 13. You can use standard SQL statements on JMP tables.  Since Big Class has only one non-unique name, Robert, for a 12 and 15 year old, I modified your request for 12, 13 and 15 year olds.

 

Names Default To Here( 1 );

dt = Open( "$Sample_Data/Big Class.jmp", invisible );

AgeList = {12, 13, 15};

_xx = Words( Char( AgeList ), "{}" )[1];  //convert to a comma delimited text and remove braces {}


ndtQ = Query( Table( dt, "t1" ), Eval Insert( "SELECT DISTINCT name From t1 WHERE age IN (^_xx^)" ) );

ndtQ << set name( "Unique BC Names" );
Wait( 3 );
Close( dt, NoSave );

  Here is the result:

image.png

tom_abramov
Level V

Re: Querying jmp data table with JSL

It is very helpful, thank you.

I am using JMP 15.

One more question - can I save the result of the query directly to list instead of creating a data table?

That is because I don't need the table, I need a list of distinct names.

If not, it still much better than Select Where --> Subset.

 

Thanks.

txnelson
Super User

Re: Querying jmp data table with JSL

Having the query create a data table is really not an inhibitor.  The script below is a simple modification of the previous script, with the addition of moving the contents of the Name column from the final data table, into a list and then deleting the data table.  As far as anyone running the script, no data tables are ever displayed.

Names Default To Here( 1 );

dt = Open( "$Sample_Data/Big Class.jmp", invisible );

AgeList = {12, 13, 15};

_xx = Words( Char( AgeList ), "{}" )[1];  //convert to a comma delimited text and remove braces {}


ndtQ = Query( Table( dt, "t1" ),invisible, Eval Insert( "SELECT DISTINCT name From t1 WHERE age IN (^_xx^)" ) );

namesList = ndtQ:Name << get values;
Close( dt, NoSave );
close(ndtQ, nosave);
Jim
gzmorgan0
Super User (Alumni)

Re: Querying jmp data table with JSL

Thank you, Jim, @txnelson .

 

@tom_abramov,  there are multple ways to do this. Since you used the word querying, I suspected you might have a more complex SQL type select so I used the JMP query()  function.  Below is a simple method not requiring SQL syntax. An associative array s a keyed list, and the keys must be unique.  :name[idx] produces a list of all rows with 12, 13 and 15 year olds. The associative array only has unique names with an associated value of 1. The unique names are the keys of the associative array. 

 

Names Default To Here( 1 );

dt = Open( "$Sample_Data/Big Class.jmp", invisible );

AgeList = {12, 13, 15};

idx = dt << get rows where(Contains(Agelist,:age));
uniq_names = Associative Array( :name[idx] ) <<get keys;
show(uniq_names);

 

tom_abramov
Level V

Re: Querying jmp data table with JSL

Thank you very much, this is exactly what I need.