Full outer Join Need at tmap transformation

One Star

Full outer Join Need at tmap transformation

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
One Star

Re: Full outer Join Need at tmap transformation

Hai,

Anyone Please suggestion me ?

Thanks,
Srinivasan.S
Community Manager

Re: Full outer Join Need at tmap transformation

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Full outer Join Need at tmap transformation

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..
Community Manager

Re: Full outer Join Need at tmap transformation

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Full outer Join Need at tmap transformation

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
Seven Stars

Re: Full outer Join Need at tmap transformation

One Star

Re: Full outer Join Need at tmap transformation

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

 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