Use Loc Sorted for Exact Match across tables -- or another tool?A customer is interested in setting up a formula in a column that is similar to VLOOKUP (excel). The function would result in the same output as using Table Join, but the customer has legitimate reasons for doing it with a formula instead of a join.
As an example, his base table might look like this:
And the second Reference table might be this:
We can assign this formula to the "Output" column of the base table:
dtRefTable = Data Table( "RefTable" );
KeyBracket = Loc Sorted( dtRefTable:Key << getvalues, :Key );
dtRefTable:SpecificInfo[KeyBracket];
When the formula executes it changes the Base table to look like this:
The problem is that in the base table, Key for Row 2 is 2401. There is no match for 2401 in the Ref Table, so Loc Sorted rounds down and selects the next lower value of Key, which (in this case) is Key=2400, and selects 111 for the output.
What the customer wants is for that cell (Output column, row 2) to be "Missing", or any other value that can be identified as "no match found" via a conditional statement. And of course it isn't as easy as just saying "if Key=2401 set cell to Missing", since there are many many possibilities in the real data for matches and non matches.
So the ideal Base table would look like:
How do we accomplish this? Is there another function (rather than Loc Sorted) that would work across tables like this?
Thanks,
Jerry
Re: Use Loc Sorted for Exact Match across tables -- or another tool?Nice! Thanks Brady, and to all who replied. I think the customer will be pleased.
JSL continues to amaze me with all the available functionality. I didn't know at all about the "Try" function, and hadn't used "As List". Very cool, and much appreciated!
Jerry
Re: Use Loc Sorted for Exact Match across tables -- or another tool?I like this better. Shorter and more elegant.
Re: Use Loc Sorted for Exact Match across tables -- or another tool?
Here is another option.
dt = Data Table( "reftable" );
Try( dt:specificInfo[Loc( As List( dt:key << getvalues ), :Key )[1]], . );
Re: Use Loc Sorted for Exact Match across tables -- or another tool?This should work as long as the values in the Key columns are numeric and you've set your table reference variable. The column referencing you had in your example doesn't seem to work in column formulas.
KeyBracket = Loc(Column(dtRefTable,"Key") << getvalues == :Key);
If(N Row(KeyBracket > 0,
Column(dtRefTable,"SpecificInfo")[KeyBracket[1]],
.
);
Not sure how I feel about code like this inside a table formula. It does save the hastle of looping over all the Key values in the target table, though. If your Key values are character, replace the "==" with a comma in the Loc command and switch the order of the arguments.
Re: Use Loc Sorted for Exact Match across tables -- or another tool?Yeah - I mean you could probably do this with formulas... If there are table control issues then what might be a better route would be something involving JMP Table Query Builder. Then they could save a standardized workflow as a QB file and it creates a table with update capabilities. The advantage QB is that the code is already formatted and built by JMP.Re: Use Loc Sorted for Exact Match across tables -- or another tool?Joins (regular or virtual) do indeed work, and I originally suggested them to the customer. However, they have some internal needs (multiple people accessing secure data files-- I don't understand all the ins and outs) that require them to do it with formulas instead of joins. Sorry!
Re: Use Loc Sorted for Exact Match across tables -- or another tool?That is what I would suggest as well. Be aware, do not close the referenced table before saving the table that has the formulas. If you do, the formulas will be removed!
Re: Use Loc Sorted for Exact Match across tables -- or another tool?Did you try virtual joins?
M
