Scenario 25: How to remove duplicate records and load only non duplicate records without using Aggregator Transformation.

Scenario 25: How to remove duplicate records and load only non duplicate records without using Aggregator Transformation.

Source
COL1
1
1
2
3
3
4

Target
COL1
2
4

Theory: The issue is grouping all duplicate records then eliminating. We will split Distinct and Duplicate records and once again we will join those two pipeline and eliminate all records which are matching from both pipeline.

Practically: 
Step 1: 
Drag and Drop your source and target.

Step 2:
Sorter
Sort the data based upon COL1

COL1 (Key Port)

Step 3:
Expression
Create Four Ports

NEW_REC_FLAG (Variable Port)= IIF(OLD_REC=col1, 2, 1)

NEW_REC (Variable Port)= IIF(col1!=OLD_REC, col1, OLD_REC)

OLD_REC (Variable Port)=NEW_REC

OUT_REC_FLAG (Output Port)= NEW_REC_FLAG

Step 4
Router
Create Two Group

New_Rec: OUT_REC_FLAG=1
Dup_Rec: OUT_REC_FLAG=2


Step 5
Sorter
Connect Dup_Rec group.

Select OUT_REC_FLAG3 as KEY Port.

Step 6
Joiner

Connect New_Rec Group to Joiner.
Connect ports from Sorter Transformation to Joiner

Join Type: Master Outer Join (Ports connected from sorter should be master port)

Condition Tab:
MASTER OPERATOR DETAIL
COL11      =                 COL13

Note: In properties tab select Sorted Input as we are joining two pipeline originated from same source qualifier.

Step 7
Filter
Connect all ports from Joiner to Filter

Filter Condition=ISNULL(COL13)

Step 8
Connect COL11 to Target from Filter.

BINGO!!!

Feel free to post your doubt in comment section or though contact form which you can find below the page.

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