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

David_Burnham
Super User (Alumni)

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
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.

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
mtrain
Level II

Re: Lookup tables using JSL

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

David_Burnham
Super User (Alumni)

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
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'.