Is it possible to use the lookup function with more than one lookup value? I have a DataSet with six columns. I would like to return the value of one column based on a lookup value match in two other columns. Based on the lookup function parameters this does not seem possible.
Is there a better way to do this? I would like to use a DataSet instead of a database table for this because it is critical and can’t rely on a database server connection always being up.
No, lookup() cannot do this. You could use the view() function from Simulation Aids like this:
try(
view('Select resultcolumnname Where col1==args[0] and col2==args[1]',
{Root Container.SourceDataset},
{Root Container.Col1MatchValue},
{Root Container.Col2MatchValue})[0,0],
fallbackValueOrReference)
view() returns a dataset with any rows that meet your criteria. The [0,0] picks the first match, or fails if no match at all. The failure is caught by try(), which then gives the fallback.