Return a Specific Row With Multiple Criteria - For Beginners

[#1]

I'm not new to JMP but I'm pretty much brand new to the scripting of this sort.  

I have a table with ≈8mm rows and 80 columns.  Ive just updated it with 3 additional columns and 2,000 rows.  I need to return a specific "Formation" from the 2,000-row update for each of the 8mm rows from the original table. 

I need to do so under a couple criteria.  IF "well.name" = "Well.Name" AND "BitDepth" < "FormationTVD" THEN "Formation".

I can break the update back out of the table if that makes more sense but I couldn't figure out how to manage this another way either.  

Lastly, I've read several discussions on this or similar topics and I've seen things similar to "dt = blahblah; dt << blah" which I don't understand at all.  If explaining this in those terms makes the most sense please do as thoroughly as you're able to.        

[#2]

Great advice @uday_guntupalli, thank you! I will absolutely work on making sure what I'm asking is more clear, and with examples, in the future.

[#3]

I think part of the problem stems from having two columns with the same name, if you ignore case: Well.Name and well.name

I tried referencing these columns using the column(dt, colname) method and it didn't work.  But using the colon prefix and column number methods worked.  Check out this code:

dt = New Table( "Untitled", Add Rows( 3 ), 
        New Column( "Well.Name", Character, "Nominal", Set Values( {"aaa", "bbb", "ccc"} ) ),
	New Column( "well.name", Character, "Nominal", Set Values( {"aaa", "ddd", "xxx"} ) )
);

// Doesn't work
found_rows = dt << get rows where(as column(dt, "Well.Name") == column(dt, "well.name"));

// Works

found_rows = dt << get rows where(:Well.Name == :well.name);

// Also works
found_rows = dt << get rows where(as column(dt, 1) == column(dt, 2));

[#4]

Thanks for this but I'm afraid I'm struggling with how I would apply the set values when they change.  I made a subset of my dataset that might help address what I'm trying to do.

The primary concern to remember is that the "WellName" and "BitDepth" are 8 million rows deep and "ActualFormation" will be as well if/when we can get a formula to work.  "WellReference", "FormationReference", and "TVDReference" are all about 2,000 rows deep with between 10-14 different formations and corresponding depths for each one.

In the attachment, the "ActualFormation" column has been manually completed to show what I need the results to do over the full 2mm rows.  

Lastly, to recap the conditions, I need the "FormationReference" for a given cell to be returned when 1) WellName = WellReference and 2) where "BitDepth" < "TVDReference".  For condition #2 I'm assuming that JMP would return the first cell reference from the top as it works its way down since there will almost always be a couple formations that meet 2 conditions.      

[#5]

Here is the attachment I just mentioned... 

[#6]

Here is the attachment I just mentioned... 

[#7]

@jlrouquette

Based on the conditions that you described - doesn't this do what you are after ? 

dt = Current Data Table(); 

dt << Select Where((:WellName == :WellReference) & (:BitDepth <:TVD Reference)); // Select Rows that meet your condition

SR = dt << Get Selected Rows; // Extract selected rows 

Show(SR); // Display results on log 

FirstRow = Min(SR); // Retrieves the first row that meets your conditions

[#8]

Yes and no.  When I run it the entire "Actual Formation" column (top to bottom) fills with 3.  The good news is that the 3rd row down in the "FormationReference" is the correct reference for the first row of the main dataset.  

For this to be perfect it would still need to 1) find different results as the formula works it way down the sheet i.e. the 5th row should have shown as 4 rather than 3, and 2) ideally, I would like it to return the actual name rather than the number (3 would equal Bell Canyon and 4 would equal Brushy Canyon. 

I could technically manage part 2 there with recoding or something similar but there are still >2,000 rows in the formation dataset I would have to manually change.  

[#9]

Hi @jlrouquette,

Forgive me if you specified this (or if this seems like an obvious question, though you did say you are new to scripting), but are you interested in jsl for a column formula, or jsl that you would run as a standalone script? In some of your posts it appears you're applying the jsl to a column formula, but many of the answers are given in terms of a script you run (a script you would make by going to File > New > New Script). For instance, the last post by @uday_guntupalli is meant to be run as a script from a window, and if placed in a column formula what would be returned is a single value (the first row that meets the criteria).

@julian

 

[#10 · Accepted solution]

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;

[#11]

@julian, it looks like you nailed it with what he was looking for in your last response. I worked with @jlrouquette via JMP Tech Support and we came to a solution very similar to yours, except yours might be more efficient because I often forget to use <<Begin/End Data Update.

Here is the solution I provided (with Begin/End Data Update added):

dt1 = Data Table( "main" );
dt2 = Data Table( "formation" );

dt1 << Begin Data Update();
// loop through each table in the main table
For( iRow = 1, iRow <= Nrow( dt1 ), iRow++,
   
   // get rows that meet the two conditions
   r = dt2 << Get Rows Where(
       And(
           dt1:well_name[iRow] == dt2:WellReference,
           dt1:BitDepth[iRow] < dt2:TVDReference
       )
   );
   
   // if we have at least one matching row, get the first formation
   If( Nrow( r ) > 0,
       dt1:ActualFormation[iRow] = dt2:FormationReference[r[1]];
   ); 
);
dt1 << End Data Update();

@jlrouquette Be sure to add the Begin/End Data Update portions like I have above so that you will see improved performance than what we just saw over the phone.