Monday, 22 June 2015

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.
Job Design
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.
Configure for Range Look Up
Step #3: Double click under Key Expression column and against Salary, following window will pop up.  
Range Expression
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.
Output
As only two employee’s fall under the given range output will show only two records.

No comments:

Post a Comment