Hi
I am merging two files using merge stage location and position as keys, in the out put i am getting duplicates records. how to eliminate duplicates and in the out put i have to get S000151 , Tom and S000152 , Kris
with my present logic i am getting S000152,Tom and S000152,Kris
any one knows logic pls help me.
Thanks in Advance.
File 1:
Name , CRMID , Position ,Location
Tom , VI, CSR, 923
Kris, SP, CSR, 923
Dana, DW, TSR, 882
File2 :
Position ID, Position, Location
S000151, CSR, 923
S000152, CSR, 923
S000153, TSR, 882
Out put File
Position ID, Name, CRMID
S000151, Tom, VI
S000152, Kris, SP
S000153, Dana, DW
How to avoid duplicates in merge stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That is a really messy requirement, whether in SQL or DataStage. If you can think of a way to do it in SQL, we would have less trouble finding a DataStage solution.
I'm confident there is no solution available using the Merge stage.
I'm confident there is no solution available using the Merge stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: How to avoid duplicates in merge stage
I guess the first thing to be done to use a merge stage is to remove duplicates, in your case the location and position are keys, and you have duplicate values. So you cannot use merge stage for this.
Re: How to avoid duplicates in merge stage
I am using merge stage for joining two files using innerjoin, not for removing duplicates. I am getting duplicates in the output of merge stage , I want to avoid those duplicates.sun rays wrote:I guess the first thing to be done to use a merge stage is to remove duplicates, in your case the location and position are keys, and you have duplicate values. So you cannot use merge stage for this.
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Re: How to avoid duplicates in merge stage
That's a bad start. Don't use the Merge stage. Load one of them into a hash, and use it as a lookup. Loading into the hash will remove duplicates, but if you care which duplicate key you use, sort the data so that the desired record comes last in a group of the same key and hence is loaded last into the hash overwriting previous values.sivap wrote:Hi
I am merging two files using merge stage...
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
I got the solution using stored pocedure.ray.wurlod wrote:That is a really messy requirement, whether in SQL or DataStage. If you can think of a way to do it in SQL, we would have less trouble finding a DataStage solution.
I'm confident there is no solution available using the Merge stage.
Thank you .
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: