Load Duplicate and Unique records separately into 2 different target tables

>> In my source table am having 2 records which are duplicated, I need to store Duplicate records and Unique records into two different tables…

 

 Reverse Engineer above datastores and make sure those are available in your model

2.      Create new mapping

3.      Drag and drop EMP, Employee_target_1  and Employee_target_2 datastore

 

Drag and drop  2 Aggregate component from component palette

 Connect output port of Source datastore (EMP) to input port of aggregate component. It will pop-up as follows. Leave it as it and click ok

Select Aggregate Component and go to property inspector à General à In Side having clause mention as “count(*) >1” and in side Manual group by clause mention as “EMPNO” and remove all attributes except EMPNO.

 

As shown in below screen

 

 

 

Drag and drop the EMP datastore once again and drag and drop Join component into mapping editor from component palette

 Join Aggregate component 1 to EMP Datastore as “AGGREGATE.EMPNO = EMP.EMPNO”

 

Connect output port of Join component to input port for Target datastore (Employee_target_1). It will pop-up then disable “Create attribute On source” and click ok

Now connect output port of Source Datastore (EMP) to input port of Aggregate component 2. It will pop-up, leave it as it and click ok

 Remove all attributes except “EMPNO” from distinct Component

 Select Aggregate Component and go to property inspector à General à In Side having clause mention as “count(*) = 1” and in side Manual group by clause mention as “EMP.EMPNO” and remove all attributes except SID

Drag and drop Join Component again into mapping editor.

Join Aggregate component and EMP1 datastore as above diagram as “AGGREGATE1.EMPNO = EMP.EMPNO

 Connect output port of join to the input port of target datastore (Employee_target_2) and click ok

 

Your Physical diagram looks like below..

 

 

IKM

 

 

Save and Execute the Mapping ..

 

 

Duplicate records loaded into Target_1:

 

 

Unique records loaded into Target_2:

 

 

***********Duplicate and Unique records loaded separately *************