topic Re: Merge two column from two differents files into one in Talend Studio

Merge two column from two differents files into one

daez — Mon, 26 Nov 2018 09:39:38 GMT

Hello everyone,

I have been struggling to solve my problem and didnt find a way on the forum. I have 2 files(maybe more after but I guess it will be done the same way), which have several columns. Here is what i would like to achieve :

File A :

Customer Random_Info Money

A xxxxxx 250

B xxxxxx 360

C xxxxxx 500

File B :

Customer Random_Info Money

J xxxxxx 150

F xxxxxx 400

S xxxxxx 600

File required :

Customer Money

A 250

B 360

C 500

J 150

F 400

S 600

The column name might be different in the files tho( like one with "Customer "& the other one "Customer_name") , and there is no ID. All my Tmap & XMLmap jobs failed ...

Thanks a lot for your help,

Re: Merge two column from two differents files into one

manodwhb — Mon, 26 Nov 2018 09:43:47 GMT

@daez,you should do fullouter join,please check the below link to implement your case.

https://community.talend.com/t5/Architecture-Best-Practices-and/How-to-do-a-full-outer-join-in-a-Talend-batch-Job/ta-p/36394

Re: Merge two column from two differents files into one

daez — Mon, 26 Nov 2018 10:12:36 GMT

Hello @manodwhb, thanks for your time.

I already tried this link, yet I dont have any ID on my files. I tried to create one but I think I'm missing something ...

I tried to do 2 files with 2 rows each, CustR & CustF are from my second files (the Lookup one). I dont manage to have my 4 rows ... Here is a screenshot of my tmap too ... Thanks for you help

Re: Merge two column from two differents files into one

manodwhb — Mon, 26 Nov 2018 10:20:43 GMT

@daez,your design is wrong,please check Example 1—Performing Two Left Outer Joins with Files in below link.

https://community.talend.com/t5/Architecture-Best-Practices-and/How-to-do-a-full-outer-join-in-a-Talend-batch-Job/ta-p/36394

Re: Merge two column from two differents files into one

daez — Mon, 26 Nov 2018 12:36:22 GMT

@manodwhb,

Yes, i tried severals possibilities but only screenshoted my latest attempt.

Still, I cant configure the Tmap as the example since i have no ID to match on. So I cant put my Match Model on " Unique match " and when I run my job its not working ... Here is a screenshot, please if you have a hint.

Re: Merge two column from two differents files into one

manodwhb — Mon, 26 Nov 2018 12:39:59 GMT

@daez,then i am pretty sure you were missing a primary and lookup selection? for example tMap1 if you use soruce 1 as a primary and source 2 as lookup,for tMap2 you have reverse like source 2 as primary and source 1 a lookup.

Re: Merge two column from two differents files into one

daez — Mon, 26 Nov 2018 12:53:40 GMT

Yes this is what I understood and did (I think).

tFileInput 1 : Source 1 (main)

tFileInput 2 : Source 2 (look up)

tFileInput 3 : Source 2 ( main)

tFileInput 4 : Source 1(look up)

Please, let me know if I am correct. (Match Model is on "All rows" because I cant save on " Unique Match")

tMap 1 :

tMap 2 :

Re: Merge two column from two differents files into one

manodwhb — Mon, 26 Nov 2018 12:55:54 GMT

@daez,you have not joined anything in your tMaps,check that?

Re: Merge two column from two differents files into one

daez — Mon, 26 Nov 2018 13:02:53 GMT

@manodwhb, Sorry but i feel rusty. Do you mean a expr key ? What can i join if there is no ID ?

Re: Merge two column from two differents files into one

manodwhb — Mon, 26 Nov 2018 13:10:45 GMT

@daez,sorry my mistake ,you take two file inputs and read those and then use tUnit to do union and the in tFilterCoulmns populate Customer and Money,populate to target.

Re: Merge two column from two differents files into one

daez — Mon, 26 Nov 2018 14:00:49 GMT

@manodwhb, Thanks its working !!! So much easier !

i didnt understand how to use tuniqrow or tFiltercolumns to not have duplicate ... ?

+ How can i choose which column i keep ?

Kindly

Re: Merge two column from two differents files into one

manodwhb — Tue, 27 Nov 2018 05:34:54 GMT

@daez,tUniquerow is the component to use the remove the duplicate values or pass the duplicate values to different output,that is not needed in your case.

Re: Merge two column from two differents files into one

manodwhb — Tue, 27 Nov 2018 05:37:27 GMT

@daez,please accept the solution the one which is correct and kudos also