Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Talend tMap Joins

by suresh

In our previous tJoin example, we explained about the Joins. In this example, we used the Talend tMap field to perform joins. 

To make this Talend tMap Joins article interesting, we are using the same data but from SQL Source. Here, we join two SQL tables using the tMap field and perform Inner Join and Left Join. The following screenshot shows the DupEmployees table data.

Talend tmap Joins 1

And the Department table data is

Talend tmap Joins 2

Talend tMap Joins Example

In this Talend tMap Joins example, drag and drop the DBConnection and tDBCommit to establish a connection to SQL and to close the same. Next, we used tDBInput to select the DupEmployees table from the Talend repository.

Talend tmap Joins 3

Next, we used another tDBInput to select the Department table from the repository.

Talend tmap Joins 4

So, we got our two tables. Next, drag and drop the Talend tMap field.

Talend tmap Joins 5

Please connect the DupEmployee Main row with tMap. Next, connect the Department Lookup row with tMap.

Talend tmap Joins 6

Next, please click on the Map Editor button or double-click on the tMap filed.

It will open the following window. We already explained all the fields in the tMap article, so please refer to the same.

Talend tmap Joins 7

First, we have to create a connection between the two tables, i.e., row1 and row2, using the key column. So, drag the DeptID from row1 and drop it in the row2 beside the DeptID.

Talend tmap Joins 8

Now, you can see the little key and row1.DepID as the expression key beside the DeptID column. It means we specified the common key columns from both the tables.

Talend tmap Joins 9

Next, click on the + button will open a new pop-up window to add an output. Let me create a new output and named it as JoinsOutput.

Talend tmap Joins 10

It will add the output field. Please drag the required fields from row1 and row2 and drop it in the JoinsOutput section.

Talend tmap Joins 11

Please click on the little Talend tMap settings button to open or expand the hidden columns to check the Join Model. As you can see from the below screenshot, it has selected the Left Outer Join as the default Join Model. Let me keep this as it is and click Apply and Ok button.

Talend tmap Joins 12

In this Talend tMap Joins example, we save the output in a SQL Database. So, drag and drop the DBOutput and select the SQL Server as the server type.

Right-click on the tMap allows you to select the already created output or created a New Output (Main). Let me select the JoinsOutput that we created earlier and join it to the tDBOutput.

Talend tmap Joins 13

As you see from the below, we are creating a new Talend_tMap_Joins (if not exists) table as the destination table.

Talend tmap Joins 14

Let us run the Talend tMap Joins job.

Talend tmap Joins 15

Let me open the Management studio to check the Talend tMap Left Outer Join result.

Talend tmap Joins 16

Please open the tMap editor to add one more output for the Rejected rows and add all the columns from row1 and row2 to RejectedRows section.

Talend tmap Joins 17

Next, click on the little settings button to open or expand the hidden fields. As you can see, by default, the tMap is not catching the rejected rows, or not catching the lookup inner join rows.

Talend tmap Joins 18

Please change both the options to True to catch the rejected rows of the Inner Join.

Talend tmap Joins 19

Next, please change the Talend Join Model from Left outer Join to Inner Join by using the browse button.

Talend tmap Joins 20

Check all the Talend tMap Join properties and click Apply and Ok button.

Talend tmap Joins 21

This time, when you right-click on the tMap, you can see the new output that we created now, i.e., RejectedRows. Please select the same and join it to tLogRow and select the Table preview.

Talend tmap Joins 22

Let me run this Talend tMap inner join job. Here, you can see those four inner join rejected rows.

Talend tmap Joins 23

And the result of the Talend tMap Inner join is

Talend tmap Joins 24

Placed Under: Talend

  • Talend Download and Install
  • Create a Talend Business Model
  • Create a Talend Job
  • Create a Global Context Group
  • Connect Talend to SQL Server
  • Context Group to connect DB
  • Talend Read Text File
  • Talend Read Excel File
  • Load Text File Data into Database
  • Load Excel Data into Database
  • Export Database Table to Excel
  • Export Database Table to TextFile
  • Export Database Table to XML
  • Export Database Table to JSON
  • Talend Aggregate Row
  • Talend Aggregate Sorted Row
  • Talend Buffer Input & Output
  • Talend Create Table
  • Talend tDenormalize
  • Talend Datatype Conversion
  • Talend tMap Type Conversion
  • Talend Filter Columns
  • Talend Filter Rows
  • Talend tMap filter rows
  • Talend tFileList
  • Talend tFixedFlowInput
  • Talend tForeach
  • Talend Fuzzy Matching
  • Talend Joins
  • Talend tMap Joins
  • Talend tMap Join Lookup
  • Talend tJava
  • Talend tJavaRow
  • Talend tJavaFlex
  • Talend tLoop
  • Talend tMsgBox
  • Talend tNormalize
  • Talend Pivot Columns
  • Talend Replace Data
  • Talend Replicate
  • Talend tRowGenerator
  • Talend tRunJob
  • Talend Rejected Rows
  • Talend Sample Row
  • Talend Sort Row Data
  • Talend SCD
  • Talend SCD Type 2 Manual
  • Talend Unite
  • Talend Unique Row
  • Talend Read Multi-Structure File
  • Talend Parent Child Job
  • Talend Execute SQL Queries
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy