Help merging 2 files with 2 IDs

[daez]

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,

[Anonymous]

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. 

[TRF]

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

[Ganshyam]

Hello,

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

Hope this solves your purpose.

Regards

Ganshyam

[daez]

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,