Merge Stage - Duplicates in the master link.

[sreelalpp]

Hi,

I need to merge two sequential files. The records from the master link have duplicates in the Merging Key field. In the records in the update link has no duplicate. In the output, I am getting merged data only for the first among the duplicate entries in the master link. In the other fields, I am getting zero. -

Ie, it finds a match and gets the data from update link only for the first among duplicate( in the merge key field) in the master link.

I was in an impression that Merge stage can be used exactly to perform lookup. Am I wrong? Is it like, the merge key field must be a primary key field in the master link ?

Below is the sample input data, output data I got and what I expected to get.

Code: Select all

From Master link

ID        Merge_Key
-----------------------
1                 A
2                 A
3                 B
4                 C
5                 C
6                 C
7                 E
8                 F

Input from Update Link:

Merge_Key              AMT
------------------------------
A                            1000
B                            2000
C                            3000
D                            4000


Output I got.

ID         Merge_Key      AMT
----------------------------------
1             A                  1000             
2             A                     0
3             B                  2000  
4             C                  3000
5             C                     0
6             C                     0
7             E                      0
8             F                      0 

What I expect  :

ID        Merge_Key        AMT
-------------------------------------
1             A                  1000             
2             A                  1000
3             B                  2000  
4             C                  3000
5             C                  3000
6             C                  3000
7             E                      0
8             F                      0 

I am keeping the unmatched rows from the master as I need them in out put with zeros.

Please be advised..

Thanks,
Sree.

Code: Select all

[balajisr]

Refer the documentation. You need to remove duplicates from the master dataset.

[madhukar]

[quote="sreelalpp"]Hi,

I need to merge two sequential files. The records from the master link have duplicates in the Merging Key field. In the records in the update link has no duplicate. In the output, I am getting merged data only for the first among the duplicate entries in the master link. In the other fields, I am getting zero. -

Ie, it finds a match and gets the data from update link only for the first among duplicate( in the merge key field) in the master link.

I was in an impression that Merge stage can be used exactly to perform lookup. Am I wrong? Is it like, the merge key field must be a primary key field in the master link ?

Below is the sample input data, output data I got and what I expected to get.

[code]From Master link

ID Merge_Key
-----------------------
1 A
2 A
3 B
4 C
5 C
6 C
7 E
8 F

Input from Update Link:

Merge_Key AMT
------------------------------
A 1000
B 2000
C 3000
D 4000


Output I got.

ID Merge_Key AMT
----------------------------------
1 A 1000
2 A 0
3 B 2000
4 C 3000
5 C 0
6 C 0
7 E 0
8 F 0

What I expect :

ID Merge_Key AMT
-------------------------------------
1 A 1000
2 A 1000
3 B 2000
4 C 3000
5 C 3000
6 C 3000
7 E 0
8 F 0 [/code]


I am keeping the unmatched rows from the master as I need them in out put with zeros.

Please be advised..

Thanks,
Sree.[code][/code][/quote]

Do a left outer join

[DSguru2B]

Please dont quote the entire message, especially if its 9 yards long

[sreelalpp]

Thanks for your responses.

balajisr,
If I remove duplicates based on the merge key from master, I will lose records. (Please see the records from the master and the merge key i am using in the sample data). Do you think, I can not use Merge to achieve this requirement ?

madhukar, This works fine if I use Left outer join. It will carry all records from left, even if duplicates are there in the join key.

Can I conclude that Merge can NOT be used to do the same functionality of Lookup if it contains duplicates in the merge key from the master ?

Thank you,
Sree

[balajisr]

Yes, results are there for you to see.

[ray.wurlod]

Can I conclude that Merge can NOT be used to do the same functionality of Lookup if it contains duplicates in the merge key from the master ?

Yes

[sreelalpp]

Thanks !