Querying jmp data table with JSL

[#1]

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

[#2]

@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:

[#3]

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.

[#4]

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

[#5 · Accepted solution]

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

[#6]

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