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
|
- 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.
