902328_en-US

liliconcom

Single Column V look up equivalent

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

 

Data Blending and CleanupRe: Single Column V look up equivalent

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

Re: Single Column V look up equivalent

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

Re: Single Column V look up equivalent

---

@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 will 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. ... or use Contains()  
   

   :VALUE_STR[Col Maximum( If( contains(:X__3 , "V3_1"), Row(), . ), :HEADER_ID )]

3. 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 )]

Re: Single Column V look up equivalent

Thanks @txnelson !

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

Re: Single Column V look up equivalent

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.

Re: Single Column V look up equivalent

I also came up with a solution based on an Associative Array

As Constant(
	Summarize( bygp = by( :HEADER_ID, :VALUE_STR ) );
	aa = Associative Array( bygp[1], bygp[2] );
);
Parse( Eval( Parse( "aa[\!"" || Char( :HEADER_ID[Row()] ) || "\!"]" ) ) );

Re: Single Column V look up equivalent

wow!

 

• Summarize with a 2 dimensional by group *)
• ... fed into an associative array to strip off the missing entries:
  Summarize sorts the entries, missing comes first - and is overwritten with the second pair
• speedup by using As Constant

 

even shorter:

As Constant(
	Summarize( bygp = by( :HEADER_ID, :VALUE_STR ) );
	aa = Associative Array( bygp[1], bygp[2] );
);
aa[Char( :HEADER_ID[Row()] )];

 

*) important: Summarize stores strings (for numeric columns as well)
->   aa[char( number  )]