Joining in tmap if DateTime falls within range

[Flippz]

I have two lists and would like to use a tmap to add information from one of them to the other: 

* List 1 (Orders): ID, Value and DateTime

* List 2 (Staff schedule): DateStart, DateEnd, PersonWorking

I want to join so that if DateTime is inbetween DateStart and DateEnd set PersonWorking in List 3 below according to List 2.

The result I'd like to get:

* List 3: (Orders and OpeningHours): ID, Value, DateTime, PersonWorking (if set in List 2)

Example

List 1::

ID, Value, DateTime

1, 100, 2021-01-01 09:23

2, 200, 2021-01-02 09:23

3, 300, 2021-01-03 09:23

List 2

DateStart, DateEnd, PersonWorking

2021-01-01 12:00, 2021-01-02 10:00, Adam 

2021-01-03 06:00, 2021-01-03 10:00, Bo

What I want in List 3 is:

ID, Value, DateTime, PersonWorking

1, 100, 2021-01-01 09:23,N/A

2, 200, 2021-01-02 09:23, Adam

3, 300, 2021-01-03 09:23,Bo

How can I achieve this? I've tried with Tmap and a join and I get out the items with ID 2 and 3 but not 1.

Kind regards,

f

[Anonymous]

Hi

I have created a demo job to achieve this, please see the below screenshots.

expression for variable isMatch:

(TalendDate.compareDate(row1.DateTime, row2.startDate)==1&&TalendDate.compareDate(row1.DateTime, row2.endDate)==-1)?true:false 

 

Hope it helps!

Regards

Shong