Developer Transformation Guide

10.4.0
- 10.5.2
- 10.5
- 10.4.1

Back Next

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:

Import a flat file physical data object that contains the sales tax amounts by county.

Create the unconnected Lookup transformation.

Add input ports to the Lookup transformation.

Define the return port.

Create the lookup condition.

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:

Rename Saved Search

Table of Contents

Developer Transformation Guide

Developer Transformation Guide

Unconnected Lookup Example

Unconnected Lookup Example

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

Step 2. Create the Unconnected Lookup Transformation

Step 3. Define the Lookup Transformation Ports

Step 4. Configure the Lookup Transformation Return Port

Step 5. Define the Lookup Condition

Step 6. Create an Expression Transformation