Accepted Solutions
Community Manager
Hi again, @jlrouquette,
After reading more carefully through this thread it seems like this boils down to having a table of lookup values that you want to update your main table with based on matching conditions. If you were simply matching based on certain columns being equal you could use Tables > Update (and specify match conditions), but since you need to match when one column is less than another, that won't work. Also, since you have a very large main table (with millions of rows), and a relatively small lookup table (around 2000 rows), I think you will get better performance going about this in reverse: rather than having a column formula that works through your main table row by row, have a script that works through your lookup row by row table to find all matching rows in your main table at once. Below is such a script. I've also attached it as a script file (which you can open and click Run on), as well as the tables you will need (the main table, and a *separate* lookup table, what I think it probably the best practice).
Some important points:
-This script will only fill a cell once. That is, once a match has been made, even if a cell matches later on in the lookup table the data in the main table will not be overwritten. This uses a JMP 13 feature of the Select Where() function to restrict a selection set. If you aren't using JMP 13 this will not work.
- The matches from this script are mostly the same as the ones you provided, but because of the point above they seem to differ. As you said, many formations match a given set of conditions and this script will simply select the first to match in the lookup table.
I suspect there is an even more efficient way to do this but is this along the lines of what you're looking for?
@julian
Names Default To Here(1);
//get handles for the tables
dt1 = Data Table( "MainTable" );
dt2 = Data Table( "LookupTable" );
//Make a column for the matching formation in the main table
dt1 << New Column("FormationReference", Character);
//for better performance, stop showing table updates until complete
dt1 << Begin Data Update;
//loop through the rows of the lookup table to find the matching cells in the main table
For(i = 1, i <= nRows(dt2), i++,
dt1 << Select Where(
(:WellName == dt2:WellReference[i]) &
(:BitDepth < dt2:TVD Reference[i]) );
// remove selection for rows that already have a match in "Formation Reference"
// note: jmp 13 or later supports restricting selection in the select where function
dt1 << Select Where(dt1:FormationReference=="", Current Selection("Restrict"));
// set the cells in FormationReference for the selected
// rows to FormationReference in the lookup table
dt1:FormationReference[dt1 << Get Selected Rows] = dt2:FormationReference[i];
);
//show table updates again
dt1 << End Data Update;
Its probably an odd fix but I couldnt figure out how to reference another table so its 2mm rows with 80 columns then an additional 3 columns at the end with only 2k rows; it is not a semetrical block of data. I used Tables==>Update.
I put in the JSL that you referenced and got the attached error message.
I corrected the column references to represent what they really are. There are two variations of well name, well.name and Well.Name. Im using them as primary keys in a way to make sure the jsl only references the formations where the well name is the same.
I dont see anywhere in the JSL where its references the formation columns (what I need returned) though. How is that happenening?
Alright, still no luck yet on this one. I liked your reply a lot because you put it in terms of the variables I'm using (incredibly helpful since I'm new to this).
My machine had a mixture of running slow and "not responding" for a couple hours after I ran the code. Not sure if its something I did wrong or not.
I attached the output and a copy of the code.
