topic Re: Single Column V look up equivalent in Discussions

Single Column V look up equivalent

shampton82 — Sun, 21 Sep 2025 00:25:32 GMT

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

Re: Single Column V look up equivalent

hogi — Sun, 21 Sep 2025 08:20:37 GMT

With the introduction of fancy new Col Aggregation, some speed issues got fixed in JMP19:

this one as well : )

Re: Single Column V look up equivalent

txnelson — Sun, 21 Sep 2025 08:30:53 GMT

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

hogi — Mon, 22 Sep 2025 07:26:59 GMT

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

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

Re: Single Column V look up equivalent

shampton82 — Sun, 21 Sep 2025 16:46:36 GMT

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

shampton82 — Sun, 21 Sep 2025 16:51:21 GMT

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

txnelson — Mon, 22 Sep 2025 01:31:23 GMT

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

hogi — Mon, 22 Sep 2025 05:36:27 GMT

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