Single Column V look up equivalent

[#1]

Hey everyone,

So I have been using a formula that works in the same way as vlookup in excel but I have now discovered that it does not work on larger data tables as it basically bogs down and kills JMP.

Attached is a sample data table and where I have split out the two functions that I had combined which works as expected:

VLU Item

VLU Result

If I were to take the formula from the "VLU Item" column and put it into the VLU Results formula this will cause the death spiral.  Having the columns be two separate ones is not a deal breaker but it would be cleaner if I could do it in one formula.

Thanks for any ideas!!

Steve

[#2]

With the introduction of fancy new Col Aggregation, some speed issues got fixed in JMP19:
New in Graph Builder for JMP 19 

this one as well : )

[#3]

Another way to do a VLookup in JMP is to use the Update platform.  This is a very efficient.  It does require that the lookup values are in a separate table, however, for your data table, that is an easy operation.  Here is a script that performs your lookup very quickly.

Names Default To Here( 1 );
dt = Current Data Table();
// Select matching cells
dt << Select Where( :VALUE_STR == "" );
// Invert current selection
dt << Invert Row Selection;
// Subset data table
// → Data Table( "Subset of Vlookup example" )
dt << Select Where( :VALUE_STR == "" );
dt << Invert Row Selection;
dtSum = dt << Subset(
	Selected Rows( 1 ),
	columns( :HEADER_ID, :VALUE_STR ),
	output table( "Lookup" )
);
// Change column name: VALUE_STR → VLU Result from UPdate
dtSum:VALUE_STR << Set Name( "VLU Result from UPdate" );
// Update data table
dt << Update(
	With( Data Table( dtSum ) ),
	Match Columns( :HEADER_ID = :HEADER_ID )
);
// Remove Lookup table
Close( dtSum, nosave );

[#4]

Thanks @txnelson !

I was wanting to keep it as a formula column but I'll keep this in my back pocket as well.

[#5 · Accepted solution]

---

@shampton82 wrote:

If I were to take the formula from the "VLU Item" column and put it into the VLU Results formula this will cause the death spiral. 

 It actually feels like a death spiral - but looking closer, it's a spiral of extreme coffee consumption.
And it woll show you how often JMP re-calculates column formulas!

The issue is caused by the

Col Maximum(if (String comparison ))

Besides abandoning the idea of a column formula, other workarounds for  JMP < 19 are:

1. DON'T use a string comparison, convert the string to a number:
   

   :VALUE_STR[Col Maximum( If( :X__3 == 3.1, Row(), . ), :HEADER_ID )]

   Tips and Tricks - best practice with JMP/JSL 
2. DON'T use col max() to "distribute" a single value in groups
    col median() is much faster:
   

   :VALUE_STR[Col Median( If( :X__3 == "V3_1", Row(), . ), :HEADER_ID )]

[#6]

Thanks @hogi !

I like your analogy better.  Also, your suggestion for use COLMEDIAN() worked perfectly.  Crazy that the two functions have such difference performances.  Just one more reason to look forward to getting 19 installed soon.