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:
Actual Salary For Employee:
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.
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
|
ID
|
Salary
|
1
|
6100
|
2
|
5100
|
3
|
1925
|
4
|
3000
|
5
|
3725
|
7
|
3514
|

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.
No comments:
Post a Comment