Exercise 4: How to load top 2 salaries for each department without using Rank Transformation and SQL queries in Source Qualifier.
Analysis/Theory: After analyzing requirement we can conclude that after sorting the data based upon salary in descending order, if we can pass only two records for each group of department then we are done. That's it!!! Is in't it too easy :-)
Practically
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, START VALUE=1 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
Yahoo!!! it was pretty easy. Keep Practicing and Learning.
Feel free to post your doubt in comment section.
Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)
If you like the approach and solution feel free to share with others.
Thanks.
hpandey.harsh@gmail.com
Analysis/Theory: After analyzing requirement we can conclude that after sorting the data based upon salary in descending order, if we can pass only two records for each group of department then we are done. That's it!!! Is in't it too easy :-)
Practically
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, START VALUE=1 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
Yahoo!!! it was pretty easy. Keep Practicing and Learning.
Feel free to post your doubt in comment section.
Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)
If you like the approach and solution feel free to share with others.
Thanks.
hpandey.harsh@gmail.com
