RANGE and SPARSE Look Up in Datastage

In previous article Look Up Stage in Datastage, we have seen how look up operation takes place in Datastage. There are two more methods through which we can perform this Look Up operation.
           1.       Range Look Up
           2.       Sparse Look Up
Let’s study them in detail one by one.
          1.      Range LooK Up:
As the name suggest, it performs look up operation for particular Range only. To understand this Range Look Up operation, consider a following scenario of two input links.
Scenario:
Suppose there are two tables of having record for employee expected salary and actual salary as below.
Expected Salary For Employee:

ID	Name	L_sal	H_sal
1	Robin	2000	4000
2	Dheeraj	2000	8000
3	Santosh	1000	4000
4	Rajiv	3500	4000
6	Shankar	2000	4000
8	Bhavnesh	2000	4000

Actual Salary For Employee:

ID	Salary
1	6100
2	5100
3	1925
4	3000
5	3725
7	3514

Step #1: Design your Job structure like below and configure above two tables with the sequential files sf_emp_expected_salary and sf_salary_det respectively as shown.

Step #2: Double click on Look Up stage, following window will pop up.
Here, define key = ID and to perform Range Look Up check the box under Range column and against Salary for stream link as shown below. Do the required mapping as shown.

Step #3: Double click under Key Expression column and against Salary, following window will pop up.  

Here we are going to give Range Expression. We need to select proper column under Input Column and Range Column.
Here, select Range Column as L_sal  and H_sal and Input Column as  Salary. Select proper Operation and Logical  expression as we have selected AND here.
One can see the detailed summary of Range Expression under Expression heading at the bottom of window.
Step #4: Compile and Run the Job. View Output on ds_trgtrange_det as below.

As only two employee’s fall under the given range output will show only two records.