How to remove duplicate rows in Informatica .

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.
Asked on November 28, 2016 in Informatica.
Add Comment
0 Answer(s)

Your Answer

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