Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Lookup tables using JSL

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 5, 2020 10:44 PM
(460 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

5 REPLIES 5

Highlighted
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Lookup tables using JSL

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Lookup tables using JSL

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Lookup tables using JSL