Talend - tMap join using conditional joins like >= <= (greater than equal to and less than equal to)

Talend - tMap join using conditional joins like >= <= (greater than equal to and less than equal to)

Sometimes we have a need to join two tables based on >= or <= condition between join columns instead of having a = (equal to) join.

A common example is that you want to join table A with table B based on date columns and you want to join these based on condition as below
tableA.date_dat >=tableB.record_start_date
and
tableA.date_dat <=tableB.record_end_date

This can be done by using "expression filter" in tmap input/output groups and putting condition there
(performance of such joins will be slow for large amount of data)

In example below  we have two flows
Flow_1 (data as below)
ID                  ID_DESC
-----------------------------
1                     ONE-FLOW1
2                     TWO-FLOW1
3                     THREE-FLOW1

Flow_2 (data as below)
ID                  ID_DESC
-----------------------------
2                     TWO-FLOW2
3                     THREE-FLOW2

Now we want to join above tables Flow_1 and Flow_2 on flow_1.ID > flow_2.ID

complete job

fixed flow input

tmap expression filter

Result of above job execution is below

[statistics] connecting to socket on port 3616
[statistics] connected
.--+-----------.
|    Flow_2    |
|=-+----------=|
|ID|ID_DESC    |
|=-+----------=|
|2 |TWO-FLOW2  ||3 |THREE-FLOW2|
'--+-----------'

.--+-----------.
|    Flow_1    |
|=-+----------=|
|ID|ID_DESC    |
|=-+----------=|
|1 |ONE-FLOW1  |
|2 |TWO-FLOW1  |
|3 |THREE-FLOW1|'--+-----------'

.---------+--------------+---------+--------------.
|                     Merged                      |
|=--------+--------------+---------+-------------=|
|ID_Flow_1|ID_DESC_Flow_1|ID_Flow_2|ID_DESC_Flow_2|
|=--------+--------------+---------+-------------=|
|3        |THREE-FLOW1   |2        |TWO-FLOW2     |
'---------+--------------+---------+--------------'

Talend enabling (unhiding) and using tHashinput and tHashoutput components

Talend tHashinput and tHashoutput (in memory data components) :

Talend provides tHash* components for storing data in memory hash. You can fetch data from any input component and store it in memory using tHashoutput. Later you can use tHashinput to read from any linked tHashoutput component and do your transformation/processing on data.

Enabling tHash* - by default these components are hidden in talend open studio (atleast till ver 5.3) To enable these go to File Menu-->Edit project properties-->PaletteSettings.
Here you can see components inside their folders - Technical is the folder which contains tHash* components. You can make this folder or components visible by bringing them to Show section of dialogbox and applying changes. I have brought tHashInput and tHashoutput to show section below.



Job below generate sample data for city code and name and then stores it in tHashoutput and next fetches this data using tHashinput and transforms this data by combining city code+city name


tHashoutput has a schema and same should be the schema for tHashinput
tHashoutput setting - you can link new tHashoutput to existing tHashoutput and append data.