topic Re: Full outer Join Need at tmap transformation in Talend Studio

Full outer Join Need at tmap transformation

Anonymous — Sat, 16 Nov 2024 13:42:12 GMT

Hi,

I have a two sources , emp & dept table.

In emp table is Source table and dept table is lookup table,
I need all the rows from source table(emp) and lookup table (dept).
In TMap transformation how to do this full outer join. In lookup table (Dept) need all the rows to target.

Thanks,
Srinivasan.S

Re: Full outer Join Need at tmap transformation

Anonymous — Thu, 05 Nov 2009 10:02:58 GMT

Hai,

Anyone Please suggestion me ?

Thanks,
Srinivasan.S

Re: Full outer Join Need at tmap transformation

Anonymous — Thu, 05 Nov 2009 10:30:53 GMT

Hello

In lookup table (Dept) need all the rows to target.

Set table (Dept) as main table and table(emp) as lookup table, don't check 'Inner join option'.
Best regards

shong

Re: Full outer Join Need at tmap transformation

Anonymous — Thu, 05 Nov 2009 11:01:30 GMT

Shong,

Thanks. but i need all the rows to target. (Full outer Join Logic),
In emp table and dept table. (both of the table all the rows need to target)

Please Suggestion me..

Re: Full outer Join Need at tmap transformation

Anonymous — Thu, 05 Nov 2009 12:38:09 GMT

Hello
You can read the source table(emp) and lookup table (dept) twice, do a left out join and a right out join, use a tUnite to merge all the rows, and then use a tUniqueRow to filter the duplicate rows. Note that select 'all matches' option when doing the left join.
Best regards
shong

Re: Full outer Join Need at tmap transformation

_AnonymousUser — Thu, 13 Sep 2012 09:09:10 GMT

Hi Shong,
any reason it is not implemented ? I've used SSIS and Merge could do full outer join, however in BODI I missed that functionality as well.
thanks,
Pjablons

Re: Full outer Join Need at tmap transformation

alevy — Fri, 14 Sep 2012 01:46:38 GMT

Vote for it: https://jira.talendforge.org/browse/TDI-5996

Re: Full outer Join Need at tmap transformation

Anonymous — Sun, 18 Dec 2016 07:13:55 GMT

Hi,
Below is the process along with screenshot (see attached) of how to perform a full outer join in Talend:
The overall logic is as follows:
Perform a union with tUnite in order to fetch all the matched and unmatched data (along with duplicates), eliminate these duplicates using tUniqueRow and finally include additional columns (if the schema of the tables/files involved in full outer join is different) in the output using tMap/tJoin.

Connect the input sources (files/tables) to tUnite component in order to fetch all the matched and unmatched data (Make sure that the schema of the tables/files involved in full outer join operation is same as tUnite only accepts similar input schema. If it is different, include only those fields which are similar in both the tables/files. We will handle the remaining fields later in the job which is explained in step #3). This will give us the union data but which includes duplicates.
In order to eliminate these duplicates, connect the result set obtained from step#1 with tUniqueRow which will give unique data. At this step, the full outer join operation for your files/tables is completed successfully if the schema (number and structure of the columns for the tables/files involved in full outer join operation) is same. If it is not same (example, one additional column in 2nd file), perform an additional step (step#3) as explained below.
Lets assume that you have two fields ID, Name in file 1 and file 2 contains ID, Name, Dept. Step#2 will give us full outer join of these files but the columns which be present in the output will be ID, Name the reason for which is mentioned in step#1. In order to pull the field Dept in the output along the result set from step#2, configure a file input component to include all the required fields (ID, Name and Dept). Join this data (tMap/tJoin) with the result set obtained from step#2, and pull all the required fields in tMap/tJoin which eventually completes the full outer join process. If file 1 also contains some additional fields which are absent in file 2, you can also include file 1 in the join operation mentioned in this step (step#3) and pull the required columns in your final result set.

Thanks,
Zaki Hasan.

Re: Full outer Join Need at tmap transformation

Anonymous — Thu, 06 Sep 2018 12:08:06 GMT

Consider this --

id,name
1,a
2,b
3,c
tMap1
id,email,address,
1,dad@gmail.com,wxy
3,adad@gmail.com,abc
4,adadaqwq@yahoo.com,poiu

o/p1

id,name,email,address
1,a,dad@gmail.com,wxy
2,b,null,null
3,c,adad@gmail.com,abc
tMap2
id,email,address,
1,dad@gmail.com,wxy
3,adad@gmail.com,abc
4,adadaqwq@yahoo.com,poiu

o/p2

id,name,email,address
1,a,dad@gmail.com,wxy
2,b,null,null
3,c,adad@gmail.com,abc

1,a,dad@gmail.com,wxy
2,b,null,null
3,c,adad@gmail.com,abc

tUnite

1,a,dad@gmail.com,wxy
3,c,adad@gmail.com,abc
4,adadaqwq@yahoo.com,poiu

1,a,dad@gmail.com,wxy
2,b,null,null
3,adad@gmail.com,abc
1,a,dad@gmail.com,wxy
3,c,adad@gmail.com,abc
4,adadaqwq@yahoo.com,poiu

Pass the row as Unique --Duplicate to get Final o/p as FoJ