How do I perform an Excel VLOOKUP in Data360 Analyze?

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 Data360 Analyze 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 Data360 Analyze, 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 Data360 Analyze, 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 Data360 Analyze example above is attached to this article. You can import the *.lna by following the instructions in our Importing data flows and library nodes section within our product documentation.