topic Re: Merging two tables in Talend Studio

Merging two tables

Anonymous — Tue, 18 Sep 2018 08:41:33 GMT

suppose I have excel file like table1 as

name branch age email

aaa computer 20 a@gmail.com

bbb civil 27 b@gmail.com

ccc IT 32 c@gmail.com

and another excel file table2 as

name age

ppp 26

qqq 28

now i want to join this but here is no any common data

output should be like

name branch age email

aaa computer 20 a@gmail.com

bbb civil 27 b@gmail.com

ccc IT 32 c@gmail.com

ppp null 26 null

qqq null 28 null

Re: Merging two tables

manodwhb — Tue, 18 Sep 2018 08:44:59 GMT

@Pankaj18,since you need to do the full outer join.

check the 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: Merging two tables

Anonymous — Tue, 18 Sep 2018 08:45:13 GMT

You should perform a FULLJOIN between the files , A leftjoin B union B leftjoin A .

Re: Merging two tables

manodwhb — Tue, 18 Sep 2018 09:21:46 GMT

@Pankaj18,still do you have issue?

Re: Merging two tables

TRF — Tue, 18 Sep 2018 09:51:34 GMT

Use a single tMap to adjust 2nd schema on 1rst one, then use a tUnite to concatenate both flows:

Here tFileInputExcel is replaced by tFixedFlowInput for the example.

And the result is:

.----+--------+---+-----------.
|         tLogRow_57          |
|=---+--------+---+----------=|
|name|branch  |age|email      |
|=---+--------+---+----------=|
|aaa |computer|20 |a@gmail.com|
|bbb |civil   |27 |b@gmail.com|
|ccc |IT      |32 |c@gmail.com|
|ppp |null    |26 |null       |
|qqq |null    |28 |null       |
'----+--------+---+-----------'

Re: Merging two tables

Anonymous — Tue, 18 Sep 2018 10:23:43 GMT

TRF ,

But its not correct to unite two different schemas right ?

Thanks .

Re: Merging two tables

Anonymous — Tue, 18 Sep 2018 10:39:24 GMT

@praveenvm wrote:

TRF ,

But its not correct to unite two different schemas right ?

Thanks .

You can quite easily make the schemas the same before you connect to the tUnite.

Re: Merging two tables

TRF — Tue, 18 Sep 2018 12:51:31 GMT

@praveenvm, that's the reason why I started my answer with "Use a single tMap to adjust 2nd schema on 1rst one".

Re: Merging two tables

Anonymous — Tue, 18 Sep 2018 13:08:55 GMT

@TRF , Ya agreed . But if we are just using the fileExcel instead of your fixedflowinput then it is adjusting the file (adding the missing columns in the file itself) and not adjusting the schema alone right ? Because even if we adjust the schema alone , that would still result in column data mismatch as talend matches file columns and schema columns by matching the order of columns and not my column names.

Thanks.

Re: Merging two tables

TRF — Tue, 18 Sep 2018 13:13:48 GMT

@praveenvm, all input schemas must be identicals to use tUnite which is required for the usecase.

Re: Merging two tables

Anonymous — Tue, 18 Sep 2018 13:58:53 GMT

@praveenvm you are forgetting about the tMap functionality. Put a tMap after the source data and before the tUnite. Schema alignment is not really that complex in this scenario

Re: Merging two tables

TRF — Tue, 18 Sep 2018 15:38:00 GMT

@Pankaj18, did this help?

If so, thank's to mark your case as solved (Kudos also acceptedas a bonus).