topic Re: Help merging 2 files with 2 IDs in Talend Studio

Help merging 2 files with 2 IDs

daez — Sun, 22 Dec 2019 22:25:28 GMT

Hi there, I thought it would be pretty easy to merge those 2 files but having a 2 columns as key is troubling for me. So here it is, if you guys can give me a hint, i would appreciate it.

ID | ID2 | Data | Data 2
| A | 1 | 50 | 70
| B | 2 | 60 | 80

ID | ID2 | Data 3 | Data 4
| A | 1 | 10 | 30

| B | 2 | | 90
| C | 1 | 60 | 80

Output needed :
ID | ID2 | Data | Data 2 | Data 3 | Data 4
| A | 1 | 50 | 70 | 10 | 30
| B | 2 | 60 | 80 | 0 | 90

| C | 1 | 0 | 0 | 60 | 80

Thanks for every info,

Re: Help merging 2 files with 2 IDs

Anonymous — Sun, 22 Dec 2019 23:20:11 GMT

You need full outer join in this case. As Talend is not giving this feature you need a workarond

https://www.youtube.com/watch?v=fMlgxVuedyk

Please see the above video so that you can get an idea.

Re: Help merging 2 files with 2 IDs

TRF — Mon, 23 Dec 2019 06:03:58 GMT

Using an inner join in a tMap, drag and drop both id and id2 from main row to lookup corresponding fields. Should work.

Re: Help merging 2 files with 2 IDs

Ganshyam — Mon, 23 Dec 2019 12:48:47 GMT

Hello,

Try using Left Outer Join with Unique Match. Join ID from table1 to ID of table2.

Hope this solves your purpose.

Regards

Ganshyam

Re: Help merging 2 files with 2 IDs

daez — Fri, 10 Jan 2020 13:55:34 GMT

Hello everyone,

Thanks for all your answer, I tried to follow your tips but still struggle, yet so close ! I tried every answer you guys, gave me, the closest I got was with this :

Yet, I still have some empty rows that shouldnt be, and if a line exist in one file but not in the second, this happen :

My t_map setup is :

File 1 = once mainrow, once lookup, same for File2.

Both t_map are made that way, Left outer & First Match

Thank you for your help,