topic Re: Specify Columns from Separate Data Tables in Discussions

Specify Columns from Separate Data Tables

robot — Fri, 01 Apr 2016 02:37:32 GMT

I intend to write a script that will match values from a column in data table 1, with the closest matched values from a column in data table 2. Is it possible to write JSL platform to allow inputs from two different data tables? If not, does anyone know of a good alternative approach?

Re: Specify Columns from Separate Data Tables

txnelson — Fri, 01 Apr 2016 05:23:14 GMT

Here is a sample script that demonstrates one of the ways you can do what you are looking for:

Names Default To Here( 1 );

// Open a sample data table

dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

// Create a couple of data tables to show how to do lookups

// to multiple tables

dt1 = dt << subset( selected rows( 0 ), columns( npn1 ) );

dt2 = dt << subset( selected rows( 0 ), columns( npn2 ) );

Close( dt, nosave );

// Sort the table to find the closest values from

dt2 << sort( by( NPN2 ), replace table( 1 ) );

// Create a column in the first table to save the row number

// from the second table that is closest to it

dt1 << New Column( "Row Number Closest" );

// Create a column that has the row numbers in the second

// data table, so it can be used to join with the first table

dt2 << New Column( "Row Number", values( 1 :: N Rows( dt2 ) ) );

// Find the value from the second table that is the closest to the

// value from the first data table

For( i = 1, i <= N Rows( dt1 ), i++,

TheSelected = dt2 << get rows where( dt2:NPN2 > dt1:NPN1 );

If( TheSelected[1] > 1,

If( dt2:NPN2[Theselected[1]] - dt1:NPN1 > dt1:NPN1 - dt2:NPN2[Theselected[1] - 1],

dt1:Row Number Closest = Theselected[1],

dt1:Row Number Closest = Theselected[1] - 1

)

);

// Join the tables based upon the closest value found

dtjoined = dt1 << Join(

With( dt52 ),OUtput Table Name("Closest Values Joined"),

By Matching Columns( :Row Number Closest = :Row Number ),

Drop multiples( 0, 0 ),

Include Nonmatches( 1, 0 ),

Preserve main table order( 1 )

);

Re: Specify Columns from Separate Data Tables

robot — Fri, 01 Apr 2016 14:54:01 GMT

Hi Jim,

Thank you for the script; I will likely use it in my implementation. I added a couple of lines to account for cases where the values in dt1 are outside the values from dt2.

Names Default To Here( 1 );

// Open a sample data table

dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

// Create a couple of data tables to show how to do lookups

// to multiple tables

dt1 = dt << subset( selected rows( 0 ), columns( npn1 ) );

dt2 = dt << subset( selected rows( 0 ), columns( npn2 ) );

Close( dt, nosave );

// Sort the table to find the closest values from

dt2 << sort( by( NPN2 ), replace table( 1 ) );

// Create a column in the first table to save the row number

// from the second table that is closest to it

dt1 << New Column( "Row Number Closest" );

// Create a column that has the row numbers in the second

// data table, so it can be used to join with the first table

dt2 << New Column( "Row Number", values( 1 :: N Rows( dt2 ) ) );

// Find the value from the second table that is the closest to the

// value from the first data table

For( i = 1, i <= N Rows( dt1 ), i++,

TheSelected = dt2 << get rows where( dt2:NPN2 > dt1:NPN1[i] );

If( N Rows( TheSelected ) == 0,

dt1:Row Number Closest[i] = N Rows( dt2 ), // Added for case: TheSelected is empty

If( TheSelected[1] > 1,

If( dt2:NPN2[Theselected[1]] - dt1:NPN1[i] > dt1:NPN1[i] - dt2:NPN2[Theselected[1] - 1],

dt1:Row Number Closest[i] = Theselected[1],

dt1:Row Number Closest[i] = Theselected[1] - 1

dt1:Row Number Closest[i] = Theselected[1]; // Added for case: TheSelected[1] == 1

)

);

// Join the tables based upon the closest value found

dtjoined = dt1 << Join(

With( dt2 ),

OUtput Table Name( "Closest Values Joined" ),

By Matching Columns( :Row Number Closest = :Row Number ),

Drop multiples( 0, 0 ),

Include Nonmatches( 1, 0 ),

Preserve main table order( 1 )

);

Re: Specify Columns from Separate Data Tables

robot — Fri, 01 Apr 2016 14:58:08 GMT

Hi Jim,

In my final script, I would like to create a platform that can be added to an Add-In, such that the user will have an interface to select columns from both dt1 and dt2. Is this possible? If not, do you know a workaround?

// Example platform input.

Names Default To Here( 1 );

dt = Current Data Table();

New Window( "Merge by Nearest Match",

<<Modal,

Text Box( "Select input" ),

H List Box(

Panel Box( "Select dt1 Column", getCols = Col List Box( dt, All ) ), // Get columns from dt1.

// Add some script to also get columns from dt2.

V List Box(

Panel Box( "dt1 column",

Lineup Box( N Col( 2 ),

Button Box( "dt1 Column", Cols1 << Append( getCols << Get Selected ) ),

Cols1 = Col List Box( N Lines( 1 ), Max Items( 1 ), Numeric )

)

Panel Box( "dt2 column",

Lineup Box( N Col( 2 ),

Button Box( "dt2 Column", Cols2 << Append( getCols << Get Selected ) ),

Cols2 = Col List Box( N Lines( 1 ), Max Items( 1 ), Numeric )

)

);

Re: Specify Columns from Separate Data Tables

txnelson — Fri, 01 Apr 2016 16:00:33 GMT

There is no restriction on populating 2 Col List Box()s from different data tables in the same display window. If you go to Help==>Scripting Index, and look at the 3rd example for the Display Box, Col List Box(), you will see an example that displays the columns from one data table, and lets you select a column. This example script can easily be expanded to be in the same display window by duplicating the code, but changing the "dt" reference to your reference variable to your second data table.

Re: Specify Columns from Separate Data Tables

robot — Fri, 01 Apr 2016 16:19:20 GMT

Thanks Jim. Do you know a function that will return a list of open data tables?

Re: Specify Columns from Separate Data Tables

txnelson — Fri, 01 Apr 2016 16:28:09 GMT

N Table() returns the count of the number of open data tables.

You can loop through them getting their names

For(I=1, I<= N Table(),I++,

show(data table(I)<<get name);

);

Re: Specify Columns from Separate Data Tables

robot — Fri, 01 Apr 2016 16:54:29 GMT

Thanks Jim!