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