Scenarios

Separating duplicate and non-duplicate rows to separate tables in informatica

source table: 
COL1
COL2
COL3
a
B
C
x
Y
Z
a
B
C
r
F
U
a
B
C
v
F
R
v
F
R
Target Table 1: Table containing all the unique rows
COL1
COL2
COL3
a
B
C
x
Y
Z
r
f
U
v
f
R 
Target Table 2: Table containing all the duplicate rows 

COL1
COL2
COL3
a
b
C
a
b
C
v
f
R
Solution
  • Drag  the source to mapping and connect it to an aggregator transformation. 
  • In aggregator transformation, group by the key column and add a new port  call it count_rec to count  the key column.
  • Connect  a router to the  aggregator from the previous step.In router make two groups one named "original" andanother as "duplicate"
  • In original write count_rec=1 and in duplicate write count_rec>1.  
  • Edit router transformation and write conditions. 
  • Connect router to two target tables. 
Powered by Blogger.