nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Single Column V look up equivalent

shampton82

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

Vlookup example.jmp

Accepted answers

hogi

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

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

... or use Contains()

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

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 )]
```

All comments

hogi

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

txnelson

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

hogi

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

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

... or use Contains()

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

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 )]
```

shampton82

Thanks @txnelson !

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

shampton82

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.

txnelson

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()] ) || "\!"]" ) ) );

hogi

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