Merging multiple tables into one

[jolio007]

INPUT DATA: 

TABLE 1

ref	CountryA_Quantity	CountryA_Sales	CountryB_Quantity	CountryB_Sales	CountryC_Quantity	CountryC_Sales
1
2
3
4
5

TABLE 2

ref	CountryA_Quantity	CountryA_Sales
1	50	1000
3	20	15

TABLE 3

ref	CountryB_Quantity	CountryB_Sales
1	100	84
2	2	4
3	789	6613

TABLE 4 

ref	CountryC_Quantity	CountryC_Sales
3	50	147
4	75	25
5	25	152

OUTPUT DATA

ref	CountryA_Quantity	CountryA_Sales	CountryB_Quantity	CountryB_Sales	CountryC_Quantity	CountryC_Sales
1	50	1000	100	84
2			2	4
3	20	15	789	6613	50	147
4					75	25
5					25	152

I really need help with this, if more clarification is necessary, don't hesitate to contact me 

[Anonymous · Accepted solution]

Hi,

    Please refer the below job flow for the resolution.

Now coming to job flow, the first part is to gather the unique ids for the flow. You will have to read data from all three files and then take unique ids after joining using tUnique. If you already have a data set containing unique ids, you can skip this step.

The unique ids will be stored in tHashOutput. You can read the data again in next Subjob using tHashInput and then do further processing.

You can get the required data by doing Left outer join with three original files.

Hope I answered your query. Please spare a second to mark the post as resolved 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[jolio007]

Thank you for your prompt reply ! 
The solution you gave is really good. I did a simplified version of my problem with only 3 column and it works perfectly for that purpose however in my real data each tables have minimum 60 columns with data that can change according to the country. So I can't have 60 x 3 columns as at the end I need to be able to narrow it down back down to only 60 columns.
The more column per country means more column in the final file which isn't what we want. Furthermore, i don't have only 3 countries, i have more than fifty, so i was wondering if you had something more.. dynamic

Cheers,

[ThWabi]

Hello jolio007,

I setup a little job to show my solution.

The Job to Join Tables

This is the configuration / setup of one of the "country tables". Adapt to your input source as needed.

Input Config/Setup

Inside the tMap, configure the joins as shown in the next picture. Use left outer join as the Join Model.

tMap Joining Tables

The job generates the following output:

Sample Output

I hope that helps or gives you an idea to build upon.

Best regards,

Thomas