To perform a lookup similar to the VLOOKUP in Excel, you can use the Lookup node, which you will find in the Correlation category of the Nodes Panel in the Dataverse Designer User Interface.
Note: you can search for "lookup" in the search box.
Example: Sales transaction data is recorded with a product ID. Perform a VLOOKUP on this product ID to the Product reference table to return the product name and cost per item.
My sales transaction data consist of the following 5 records, where:
- 'id' is the unique transaction identifier
- 'productID' uniquely identifies the product that was sold, and
- 'qty' is the quantity sold
My Products reference table is the list of the products that are for sale, where:
- 'productID' is the unique product identifier,
- 'product' is the product name, and
- 'pricePerItem' is the price for a single product
The product identifier 'productID' appears in both data sets so I can use this as the field to perform the base the lookup on.
Using a VLOOKUP in Excel, I use the productID as the lookup_value, select the product table as the table_array and specify that I want to return the second column (product name). Then I can do the same for the price per item by returning the third column (pricePerItem):
In Dataverse, I return both the product name and price per item in a single step using the Lookup node.
I connect a Lookup node to the sales transaction data and product reference table - with the product reference table as the second input (i.e the lookup table) and set the following:
- InputKey = 'productID'
- LookupKey = 'productID'
The output of the Lookup node is:
What if my lookup table has more than one record that matches?
A VLOOKUP on exact match will the first record where the join is satisfied. Depending on your lookup table, it may not be appropriate for there to be multiple potential matches.
In Dataverse, you have the option to return the first matching value or you can be notified if there are more than one match - this might be an indication that there is an issue with the reference table.
Make a selection in the drop-down for the 'DuplicateKeyBehaviour' property:
The Dataverse example above is attached to this article. You can import the *.lna by following the instructions in the section titled "Importing data flows and library nodes" within the Dataverse Help > Advanced topics > Sharing data flows and library nodes help section.