Range Lookup Example

We can define a range lookup either on the stream link or a reference link of a Lookup stage.

· Range on the reference link:

Here, two columns on the reference link define the range. A match occurs when a selected value in the source row is within the range.

· Range on the source link:

Here, two columns on the source link define the range. A match occurs when a selected value in the reference row is within the range.

Example:

In below example, we have 2 input files i.e. ‘Emplyoee.txt’ and ‘Job_Desc.txt’.

Here we are going to perform a range lookup on both the files on a key column ‘employeeID’ where the Range lookup condition will be ‘Hiredate’ of employee needs to be in the range of provided job ‘Start date and End date’ and pushing the data to the target Dataset file.

Input Data:

1. Employee.txt contains Employee data.

input Emp - Range Look up Example

      2. Job.txt contains employee’s Job description data.

input Job - Range Look up Example

Design the job as shown below:

jobDesn 1 - Range Look up Example

Open Lookup stage properties window, we can see the columns from both input files and a blank output. Here we need to lookup ‘employeeID’ column and provide the Range for look up and pull the data to target columns.

Select the ‘employeeID’ column from Lnk_Emp to ‘employeeID’ column of Lnk_Job which is an ‘Equality match lookup’ option. And Select the ‘HireDate’ column from Lnk_Emp and select ‘Range lookup’ option from the drop down.

2 1024x547 - Range Look up Example

Range Look up dialog property box will open as shown below. For ‘HireDate’ column from Lnk_Emp select the ‘Range Column’ and Equality and logical operator to define the range as shown below.

4 - Range Look up Example

Provide the Lookup conditions as per the requirement.

3 1 - Range Look up Example

Save, Compile and run the job. After successful job run we can see the result as below.

output - Range Look up Example

Output Data:

RangeLookup OutputData - Range Look up Example

Reject Data:

RejData 1 - Range Look up Example