How to remove duplicate rows in Informatica .

Want create site? Find Free WordPress Themes and plugins.

Scenario:Route unique rows to one table and duplicate to another using Informatica transformations. 
SOURCE:

COL1 COL2 COL3
100 101 102
10 11 12
100 101 102
999 998 997
100 101 102
1 2 3
1 2 3

Target Table 1: Table containing all the unique rows

COL1 COL2 COL3
100 101 102
10 11 12
999 998 997
1 2 3

Target Table 2: Table containing all the duplicate rows

COL1 COL2 COL3
100 101 102
100 101 102
1 2 3

We need to use transformations in such a way that Source table duplicate values are filter into separate table. As you guessed we need to use both Router and Aggregator

  • Bring the source table and connect to an Aggregator Transformation
  • Add a new column in Aggregator (group by key column) for eg. Count_rec having the count of the key column
  • Connect to Router with two groups

1.ORGINAL: write count_rec=1

2.DUPLICATE: write count_rec>1.

  •  Connect two groups to corresponding target table.
Did you find apk for android? You can find new Free Android Games and apps.
Asked on November 28, 2016 in ETL.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.