Lookup Function

The Lookup function enriches your data by adding fields from a reference table based on key matching. It's similar to a Left Join in Merge but optimized for the common pattern of looking up values from a reference source.

How It Works

Lookup takes two inputs:

• Main data - Your primary dataset (all rows are preserved)
• Reference data - The lookup table (matched to add fields)

For each row in main data:

1. Find matching row(s) in reference data by key
2. Add specified fields from the matched reference row
3. If no match found, fields are null
4. Main data rows are never duplicated or removed

Lookup vs. Merge

Aspect	Lookup	Merge (Left Join)
Purpose	Add specific fields	Combine all columns
Field selection	Explicit	All or configured
One-to-many	Returns first match	Creates multiple rows
Configuration	Simpler	More options
Performance	Optimized for lookups	General purpose

Use Lookup when:

• Adding a few fields from a reference table
• You want exactly one match per row
• The pattern matches VLOOKUP/INDEX-MATCH

Use Merge when:

• Combining datasets with many fields
• You need control over join type
• One-to-many results are acceptable

Adding a Lookup

1. Drag Lookup from the Functions section of the Element Panel
2. Connect your main data source as the primary input
3. Connect your reference data source as the lookup input
4. Click the Lookup node to configure

Configuration Panel

Source Configuration

Main Data Source:

• The primary dataset that flows through
• All rows preserved in output
• Fields remain unchanged except for added lookup fields

Lookup Source:

• The reference table to look up values from
• Only matched rows contribute fields
• Can be an Entity, result of another node, or parameter

Lookup Key

Specify how to match rows:

Single Key:

Main Key	Lookup Key
CategoryID	ID

Matches when CategoryID equals ID in lookup table.

Multiple Keys (Composite):

Main Key	Lookup Key
Year	FiscalYear
Region	Territory

Matches when ALL key pairs are equal.

Fields to Add

Select which fields from the lookup table to add:

Configuration:

Lookup Field	Output Name
CategoryName	Category
Description	CategoryDescription

Field options:

• Lookup Field - The field in the reference table
• Output Name - The name in your output (can rename)

Match Options

When Multiple Matches Exist

First Match (default): Returns the first matching row found.

Last Match: Returns the last matching row found.

Error: Fails if multiple matches exist.

When No Match Exists

Return Null (default): Added fields are null.

Use Default Value: Specify default values for each field.

Error: Fails if no match found.

Cache Options

Cache Reference Data: Loads reference table into memory for faster lookups.

Best for:

• Small to medium reference tables
• Multiple lookups against same reference
• Static reference data

Avoid when:

• Reference table is very large
• Reference data changes during execution

Common Use Cases

Category Names

Main Data: Products with CategoryID Lookup: Categories (ID, CategoryName)

Goal: Add CategoryName to each product

Configuration:

• Key: CategoryID = ID
• Fields: CategoryName

Before:

ProductID	ProductName	CategoryID
P001	Widget	C01
P002	Gadget	C02

After:

ProductID	ProductName	CategoryID	CategoryName
P001	Widget	C01	Electronics
P002	Gadget	C02	Accessories

Status Descriptions

Main Data: Orders with StatusCode Lookup: StatusCodes (Code, Description, Color)

Goal: Add readable status descriptions

Configuration:

• Key: StatusCode = Code
• Fields: Description as StatusDescription

Country Information

Main Data: Customers with CountryCode Lookup: Countries (Code, Name, Continent, Currency)

Goal: Expand country code to full details

Configuration:

• Key: CountryCode = Code
• Fields: Name as CountryName, Currency

Employee Manager Names

Main Data: Employees with ManagerID Lookup: Employees (EmpID, FullName)

Goal: Add manager's name

Configuration:

• Key: ManagerID = EmpID
• Fields: FullName as ManagerName

Currency Conversion

Main Data: Transactions with Currency and Amount Lookup: ExchangeRates (Currency, RateToUSD)

Goal: Add exchange rate for conversion

Configuration:

• Key: Currency = Currency
• Fields: RateToUSD

Then Transform: AmountUSD = Amount * RateToUSD

Configuration Values

Main Data: Processing records Lookup: Config (Key, Value)

Goal: Add configuration parameters

Configuration:

• Key: ConfigKey = Key
• Fields: Value as ConfigValue

Multiple Lookups

Chain lookups for multiple reference tables:

Scenario: Products need Category and Supplier info

Flow:

[Products] → [Lookup: Categories] → [Lookup: Suppliers] → [Output]

First Lookup:

• Key: CategoryID
• Fields: CategoryName

Second Lookup:

• Key: SupplierID
• Fields: SupplierName, SupplierCountry

Handling Lookup Failures

No Match Found

Scenario: Some records don't have a matching reference entry

Options:

1. Accept nulls:

   • Default behavior
   • Missing lookups result in null fields
   • Handle nulls downstream with COALESCE

2. Default values:

   • Configure defaults: CategoryName: "Unknown"
   • Applied when no match exists

3. Filter after lookup:

   • Add Filter: LookupField IS NOT NULL
   • Removes rows without matches

4. Flag for review:

   • Transform: HasCategory = ISNOTNULL(CategoryName)
   • Filter or report rows without matches

Data Quality Checks

After Lookup Transform:

LookupStatus = CASE(
  ISNULL(${CategoryName}), "Missing Category",
  ISNULL(${SupplierName}), "Missing Supplier",
  "Complete"
)

Filter for issues:

LookupStatus != "Complete"

Lookup Performance

Optimizing Reference Tables

Small reference tables (under 10,000 rows):

• Enable caching
• Fast memory-based lookups
• Load once, use many times

Large reference tables:

• Consider filtering reference data first
• Only include needed columns
• Evaluate if Merge is more appropriate

Key Considerations

Indexed keys:

• If reference is from database, indexed columns perform better
• Primary keys and foreign keys are ideal

Data types:

• Ensure key types match
• Type conversion happens but adds overhead

Multiple Lookups

When doing many lookups in sequence:

• Cache small reference tables
• Consider combining into fewer lookups
• Filter main data early to reduce lookup volume

Troubleshooting

All Lookup Fields Are Null

Possible causes:

1. Keys don't match

   • Check data types (text vs number)
   • Check for whitespace or case differences
   • Preview both sources to compare values

2. Reference table is empty

   • Verify reference source has data
   • Check any filters on reference

3. Wrong key field selected

   • Verify key mappings
   • Ensure correct fields are connected

Debugging:

1. Preview main data - note key values
2. Preview lookup data - verify matching values exist
3. Try matching a specific known value

Some Lookups Fail

Partial matches indicate:

• Some key values exist in reference, others don't
• This may be expected (new codes, legacy data)

Resolution:

• Use default values for expected missing entries
• Filter out rows that shouldn't have matches
• Update reference table with missing entries

Wrong Values Returned

Possible causes:

1. Multiple matches - first one returned might not be intended
2. Wrong key used - different field matched

Resolution:

• Make reference keys unique
• Add additional key fields (composite key)
• Sort reference appropriately if using first match

Duplicate Rows in Output

Note: Lookup should never create duplicates.

If you see duplicates:

• They existed in main data before Lookup
• Use Distinct before or after Lookup

Examples

Product Catalog Enrichment

Main Data: Product IDs from orders Lookups:

1. Products table → Name, Price
2. Categories table → CategoryName
3. Inventory table → StockLevel

Flow:

[Order Items] → [Lookup: Products] → [Lookup: Categories] → [Lookup: Inventory] → [Output]

Employee Directory

Main Data: Employee records Lookups:

1. Departments → DepartmentName
2. Locations → LocationName, Address
3. Employees (self) → ManagerName

Result: Complete employee directory with department, location, and manager names

Transaction Reporting

Main Data: Financial transactions Lookups:

1. Accounts → AccountName, AccountType
2. CostCenters → CostCenterName
3. Projects → ProjectName, ProjectManager

Result: Transactions with all reference data for reporting

Localization

Main Data: UI elements with key codes Lookup: Translations (KeyCode, Language, Text)

Key: ElementKey = KeyCode AND LanguageCode = Language

Result: Localized text for each UI element

Best Practices

Keep Reference Tables Clean

• Remove duplicates from reference keys
• Validate reference data quality
• Update references before lookups

Use Meaningful Defaults

When matches might not exist:

Default for CategoryName: "Uncategorized"
Default for Status: "Unknown"
Default for Rate: 0

Add Source Indicators

When lookups might fail, indicate the source:

Transform after lookup:
CategorySource = IF(ISNOTNULL(${CategoryName}), "Matched", "Default")

Validate Results

After important lookups:

1. Count nulls in lookup fields
2. Verify expected match percentage
3. Sample check for correct values

Next Steps

• Merge Function - More complex joining
• Transform Function - Calculate values after lookup
• Filter Function - Filter based on lookup results
• Building Flows - Complete workflow guide