Developer Transformation Guide > Lookup Transformation > Unconnected Lookup Example
  

Unconnected Lookup Example

A retail store in California adds a state sales tax to each price for items that it sells to customers within the state. The amount of tax is based on what county that the customer resides in. To retrieve the sales tax, you create a Lookup transformation that receives a county name and then returns a sales tax amount for the county. If the county does not charge a sales tax, the Lookup transformation returns NULL. Call the lookup from an Expression transformation.
Complete the following steps to configure an unconnected lookup of sales tax by county:
  1. 1. Import a flat file physical data object that contains the sales tax amounts by county.
  2. 2. Create the unconnected Lookup transformation.
  3. 3. Add input ports to the Lookup transformation.
  4. 4. Define the return port.
  5. 5. Create the lookup condition.
  6. 6. Call the lookup from an Expression transformation.

Step 1. Import the sales tax lookup source to the Model repository.

The sales tax file must be in the Model repository before you create the Lookup transformation. For this scenario, the sales tax file contains two fields, Sales_County and County_SalesTax. The county is a string that contains a county name. County_SalesTax is a decimal field that contains a tax rate for the county. The sales tax file is the lookup source.

Step 2. Create the Unconnected Lookup Transformation

Create a reusable flat file Lookup transformation with the sales tax flat file data object. For this scenario, the transformation name is Sales_Tax_Lookup. Select Return First Row on multiple matches.

Step 3. Define the Lookup Transformation Ports

Define the Lookup transformation ports on the Ports tab of the Properties view.
Port Type
Name
Type
Length
Scale
Input
In_County
String
25
Output
SalesTax
Decimal
3
3

Step 4. Configure the Lookup Transformation Return Port

The return port is the field in the flat file that the lookup retrieves. On the Columns tab, the County_SalesTax column is the return port.
When the lookup is true, the Integration Service finds the county in the flat file source. The Integration Service returns a sales tax value in the return port. If the Integration Service does not find the county, the lookup result is false and the Integration Service returns NULL in the return port.

Step 5. Define the Lookup Condition

On the Lookup view, define the lookup condition to compare the input value with values in the lookup source.
To add the lookup condition, click the Lookup Column.
The lookup condition has the following syntax:
SALES_COUNTY = IN_COUNTY

Step 6. Create an Expression Transformation

Create an Expression transformation that receives sales records from a flat file. The Expression transformation receives a customer number, sales amount, and the county of the sale. It returns the customer number, sales amount, and a sales tax.
The Expression transformation has the following ports:
Port Type
Name
Type
Length
Precision
Default Value
Input
County
String
25
10
Pass-through
Customer
String
10
Pass-through
SalesAmt
Decimal
10
2
Output
SalesTax
Decimal
10
2
0
The SalesTax port contains a :LKP expression. The expression calls the Sales_Tax_Lookup transformation and passes the county name as the parameter. The Sales_Tax_Lookup transformation returns the sales tax rate to the expression. The Expression transformation multiplies the tax rate by the sales amount.
Enter the following expression for the SalesTax port:
(:LKP.Sales_Tax_Lookup(County) * SalesAmt)
The SalesTax port contains the expression result. If the lookup fails, the Lookup transformation returns NULL and the SalesTax port contains null values.
You can add an expression to check for null values in the SalesTax port. If SalesTax is NULL you can configure the SalesTax port to return zero. Add the following text to the lookup expression to check for null values and return zero:
IIF(ISNULL(:LKP.Sales_Tax_Lookup(County) * SalesAmt),0, SalesTax)