Choose Language Hide Translation Bar
Highlighted
tom_abramov
Community Trekker

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 );
0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
gzmorgan0
Super User

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
Highlighted
gzmorgan0
Super User

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

Highlighted
tom_abramov
Community Trekker

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.

0 Kudos
Highlighted
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
Highlighted
gzmorgan0
Super User

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

tom_abramov
Community Trekker

Re: Querying jmp data table with JSL

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

0 Kudos