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     |
'---------+--------------+---------+--------------'