Monday, 24 March 2014

How to load top 2 salaries for each department without using Rank Transformation and SQL Queries in Source Qualifier?

Step 1:
Drag and drop one source and three instance of target table.
Step 2: 
Sorter
Sort the data based upon SAL.
Select SAL as key port and Descending order
Step 3
Router
Create three groups.
Dept10_Data =  Dept=10
Dept20_Data =  Dept=20
Dept30_Data =  Dept=30
Step 4
Sequence Generator
Create Three sequence Generator Transformation
(Change END VALUE =2 and Check Cycle) 
By this way your mapping will always give you your expected output.
This step we need to assign an ID for each and every group of records. (If you don't want to use Sequence generator use Expression and build a logic to assign an unique id for each and every record but in that case also we need three different Expression transformation :-)
Step 5
Filter
Create three different Filter Transformation
Connect from each group of Router Transformation to different Filter Transformation like below
Dept10_Data To Filter 1
Dept20_Data To Filter 2
Dept30_Data To Filter 3
Develop Filter condition = NEXTVAL <=2 in all Filter Transformation.
Step 6
Connect all three instance of target from different Filter Transformation.
 Filter 1 To Target Instance 1
 Filter 2 To Target Instance 2
 Filter 3 To Target Instance 3


Thanks
Ur's Hari

No comments:

Post a Comment