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.
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.
1. Employee.txt contains Employee data.
2. Job.txt contains employee’s Job description data.
Design the job as shown below:
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.
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.
Provide the Lookup conditions as per the requirement.
Save, Compile and run the job. After successful job run we can see the result as below.