Lookup tables using JSL

[#1]

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]

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.

[#3 · Accepted solution]

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

[#4]

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

[#5 · Accepted solution]

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.

[#6]

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