Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.