Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
mtrain
Level II

Lookup tables using JSL

Hi,

 

I wrote some code to implement a rudimentary lookup table for a voltage/time dataset. But because of the size of my dataset, it is freezing up.

 

Quick background, there might be around 1000 rows (time intervals) for each run and there are around 1500 runs, so 1.5m rows in total. In a nutshell, I am doing the following in my code before I start using the lookup table:

- Process dataset

- Calculate dV/dt from data

- Use tabulate to find the peak dV/dt for each individual run and create a new data table called dt2.

- Create a new column in the dataset to write the peak dV/dt value next to each dV/dt value in each run.

 

At this point my code starts going line by line through dt1, cross checking the different test parameters that define the # run against dt2 and finding the peak dV/dt and returning it to that line in dt1.

 

My code for searching the lookup table looks like this:

Current Data Table( dt1 );

For Each Row( v = :sample; w = :temp; x = :cell; y = :vicl; z = :v_bg;
// Find the row in dt2 that matches v/w/x/y/z row = dt2 << get rows where( :sample == v & :temp == w & :cell == x & :vicl == y & :v_bg == z);
// get values from the dV/dt peak column in dt2 col = Column(dt2, "dvdt_abs_max") << getValues;
// pick out the value of row[n] from that column and save into the active row in dt1 :dVdt_peak = col[row] );


This code worked when I tested it for a small set, but once I had my complete dataset, the infinite spinning wheel appeared. I'm pretty new to this and am sure there must be a better more efficient way to do this, I just haven't figured it out.

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: Lookup tables using JSL

You are really making JMP do this the hard way.  The easiest and most efficient way to handle this is to use the Update Platform.  It should handle your lookup on 1.5m rows without an issue

 

dt << Update(
	With( dt2 ),
	Match Columns( :sample == :sample, :temp == :temp, :cell == :cell, :vicl == :vicl, :v_bg == :v_bg ),
	Add Columns from Update Table( :dvdt_abs_max )
);
Jim

View solution in original post

Highlighted
David_Burnham
Super User

Re: Lookup tables using JSL

See @txnelson's response, but also note that whilst 'For Each Row' is nice to write, my experience of it is that it executes very slowly.  A standard for-loop i.e. 'For (i=1,i<=nrows(dt),i++' is a much faster method for iterating over the rows of a table.

-Dave

View solution in original post

5 REPLIES 5
Highlighted
mtrain
Level II

Re: Lookup tables using JSL

Just quickly if it helps, the reason for this code is so that I can filter and delete rows based on whether they fall under some threshold that is a function of the peak dV/dt.

Highlighted
txnelson
Super User

Re: Lookup tables using JSL

You are really making JMP do this the hard way.  The easiest and most efficient way to handle this is to use the Update Platform.  It should handle your lookup on 1.5m rows without an issue

 

dt << Update(
	With( dt2 ),
	Match Columns( :sample == :sample, :temp == :temp, :cell == :cell, :vicl == :vicl, :v_bg == :v_bg ),
	Add Columns from Update Table( :dvdt_abs_max )
);
Jim

View solution in original post

Highlighted
mtrain
Level II

Re: Lookup tables using JSL

This function is exactly what I was looking for. Thank you @txnelson!

Highlighted
David_Burnham
Super User

Re: Lookup tables using JSL

See @txnelson's response, but also note that whilst 'For Each Row' is nice to write, my experience of it is that it executes very slowly.  A standard for-loop i.e. 'For (i=1,i<=nrows(dt),i++' is a much faster method for iterating over the rows of a table.

-Dave

View solution in original post

Highlighted
mtrain
Level II

Re: Lookup tables using JSL

Thanks for the pointer @David_Burnham, I will remember that next time I find myself using 'For Each Row'.