How to Combine data by tmap using Multiple Lookup Files?

In this post will see how to combine the data from multiple lookups using tMap talend components.
This is similar to JOIN in SQL queries where we join multiple tables based on matching fields.

This is Main Input File

Orders

Order_ID,Customer_ID,Employee_ID,Order_Date,Shipper_ID,,
11248,1,1,41655,3,,
11248,2,1,41656,1,,
11248,4,1,41657,4,,
11249,9,3,41658,1,,
11249,10,8,41659,1,,
11250,11,2,41660,1,,
11250,13,6,41661,3,,
11250,6,7,41662,2,,
11251,7,6,41663,2,,
11251,3,4,41664,4,,
11251,2,4,41665,2,,
11252,11,7,41666,1,,
11252,7,3,41667,2,,
11252,5,4,41668,1,,
11253,8,4,41669,4,,

And these are three Lookup Files

Customers

Customer_ID,Customer_First_Name,Customer_Last_Name,Customer_Address,Customer_City,Customer_PostalCode,Customer_Country,RegisterDate
1,Lee,Sime,722 Bur Oak Avenue ,Berlin,12232,Germany,1/13/2010
2,William,Hinkle,543 ANJOU ANJOU H1K 2P4,Mexico,71450,Mexico,10/4/2007
3,Nick,Petrakis,40  Toronto M8Z 3Z7,Mexico,48577,Mexico,3/16/2000
4,Jian,Paysnoe,11 Collaroy  2093,London,56008,UK,1/13/2010
5,Min,Parks,223 Wellington  5011,Berlin,32456,Germany,10/4/2007
6,Joseph,guo,324 Tman Street   Stafford Heights ,Mannheim,43567,Germany,3/16/2000
7,Justin,Ash,90 Ruapehu Road   Ohakune ,Strasbourg,67000,France,1/13/2010
8,Ming,Ho,32 Wilfred road,Madrid,33456,Spain,10/4/2007
9,David,French,Williamstown Road,Marseille,45668,France,3/16/2000
10,GORDON,baleri,East 7th Avenue   Vancouver Vancouver,Tsawassen,98977,Canada,1/13/2010
11,Aaron,Vanzin,431 algary Calgary ,London,56785,UK,10/4/2007
12,Gregory,wang,29 View Royal Ave   Victoria Victoria,Buenos Aires,54367,Argentina,3/16/2000
13,George,Loomis,16 Martel   Beloeil,Mexico,43256,Mexico,1/13/2010
14,JACOB,Jiang,13 Surrey Close,Bern,35843,Switzerland,10/4/2007

Employees

Employee_ID,Last_Name,First_Name,Birth_Date
1,Lasson,Samuel,7/2/1969
2,Ortega,lee,9/19/1928
3,Zant,Thi,3/23/1938
4,Cohen,John,4/21/1927
5,Park,Umar,12/8/1968
6,Knipp,Troy,2/19/1952
7,Lunberg,Greg,8/30/1963
8,Brown,Sami,9/19/1958
9,Barnhill,Pascal,7/2/1969
10,Rose,Aaron,2/13/1956

Shippers

Shipper_ID,Shipper_Name,Phone
1,Shiny Shipping,4133694044
2,Rose Marry Ship Pvt,5703453631
3,Nick Ltd,3038141672
4,Michle Ltd,7274882222

Now first create the metadata of the main input delimited Ordesr file,Customers file,Employees and Shippers file .
Drag and drop metadata created of Orders file,Customers file,Employees and Shippers file in to the Job designer and select tFileInputdelimited component.

Drag and drop tFileOutputDelimited from the palette into design workspace then join each input and output file to tmap by right clicking on input file then select Row > Main.Here Order File will be the Main file and other three files are Lookup.

Now double click on tmap you will see this window then start mapping.

Here first we drag from row1 column Customer_ID to row2 column Customer_ID in the same way you drag Employee_ID to row 3 and Shipper_ID to row 4.

 Click the tMap settings button on row 2, and click the three-dot button corresponding to Join Model, and let it be Left Outer Join and click OK button .In the same way do for row 3 and row 4 also.

                                           

                                                             

Next drag from row 1 column Order_ID,Customer_ID,Employee_ID and Shipper_ID to output column.Now we want to concatenate  Customer_First _Name and Customer_Last _Name by simply dragging each of them to single field on the output side of the map editor.

Now by clicking the button labelled ....corresponding to column Customer_First_Name then it will open the Expression Editor.

Cut and paste the following code in the Expression Builder 

row2.Customer_First_Name+" "+row2.Customer_Last_Name 

This code will simply concatenate the Customer first name and last name.

Do the same same process for row 3 First_Name  and Last_Name .

row3.First_Name+" "+row3.Last_Name 

Now tmap map screen appears like this.Click Apply then OK button.

Click on tFileOutputDelimited_1 and if you want a new file to be created, browse to the destination output folder and type a file name for example:-

"C:/Talend_Workspace/TALEND_TUTORIALS/lookup_output/Order_Details.csv"and tick the checkbox of Include Header.

                                                

At last Run the job.

                                                

 Here the output files are created, which contain the relevant data as defined.