How to get unique records ?

bond88 · Post by **bond88** » Wed Jul 03, 2013 12:35 pm

Hi all,
I am loading data into target using oracle connector and I am using funnel to merge the data from three 3 different sources (Oracle sources). I observed there are some duplicates on target. So, First I want to take records from two sources and later I want to add all the records from the third source whichever not existed in the first two sources. Could you please suggest me a way to achieve this?

Thank you,

jwiles · Post by **jwiles** » Wed Jul 03, 2013 3:58 pm

One possible solution, assuming you are using IS 8.5 or later:

Add a column with 1, 2 or 3 to identify which source the record belongs to
EDITED
Partition the combined records on the key columns
Sort on the key columns and source column
In a transformer:
- identify key changes by comparing the incoming record's key columns to the previous record's
- If the key has changed and the incoming record is source 3, set a stage variable to @TRUE or 1, otherwise set it to @FALSE or 0. If the key hasn't changed, keep the current value of that stage variable
- In a constraint keep all source 1 and 2 records and all records when the stage variable above is set to @TRUE or 1
/EDITED

Realized that my original reply was incorrect

Regards,

srinivas.nettalam · Post by **srinivas.nettalam** » Fri Jul 19, 2013 4:49 am

Split the Combined records from first 2 sources into two streams using copy stage and send one stream of the combined data from first 2 sources as reference to Lookup stage and use the third source as stream , perform look up on key and capture the rejects. Now send the other stream from copy stage and the rejects from lookup to funnel.