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,
How to get unique records ?
Moderators: chulett, rschirm, roy
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 134
- Joined: Tue Jun 15, 2010 2:10 am
- Location: Bangalore
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.
N.Srinivas
India.
India.