left join with date conditions

[varun_b]

Hi,

Can anyone plz help on how to do left join with date condition in tmap?

like we do in mysql:

T1 table

EMP_ID|trans_date

1|2019-11-29

2|2019-11-30

2|2019-12-02

T2 table

EMP_ID|EMP_BELT|START_DATE|END_DATE

1|Green|2019-11-11|null

2|yellow|2019-12-01|2019-12-20

2|green|2019-12-21|null

Expected Output:

Emp_id,trans_date,belt

1,2019-11-29,Green

2,2019-11-30,null

2,2019-12-02,yellow

2,2019-12-23,green

sql query would be:

select t1.EMP_ID,t1.trans_date, t2.EMP_BELT

from t1 

left join t2 on t1.c1 =t2.c1

AND (t1.trans_date >=t2.start_date AND(t1.trans_date<=t2.end_date OR t2.end_date is NULL)

Regards,

Varun

[Anonymous]

Hi,

    It seems you are joining on column c1 and the date conditions you have mentioned is a filter condition.

    You can do the same in Talend where join condition can be on c1 column and add the below to output filter expression in tMap.

(t1.trans_date >=t2.start_date AND(t1.trans_date<=t2.end_date OR t2.end_date is NULL)

   Could you please try it out and let me know?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[varun_b]

Hi Nikhil,

in expression filter of tmap Im not able to give directly <= and >= oprator on date columns so I used (TalendDate.compareDate(t1.trans_date,t2.FLD_START_DATE,"yyyy-MM-dd HH:mm:ss") >=0 &&
(Relational.ISNULL(t2.FLD_END_DATE)||t2.FLD_END_DATE == null || (TalendDate.compareDate(t1.trans_date,t2.FLD_END_DATE,"yyyy-MM-dd HH:mm:ss")<1)) )

But this is giving only records with belt which transaction are done b/w given dates

like:

1,2019-11-29,Green

2,2019-12-02,yellow

2,2019-12-23,green

where trans_date=2019-11-30 of emp 2 where he was not having any belt dint populate:

2,2019-11-30,null

[Anonymous]

Hi @varun_b ,

Could you change the match model in tmap to All Matches and try once?

[varun_b]

Hi ,

Yep, I have selected All matches only. I have attached actual tmap screenshot.

Marked in yellow is my condition.

kindly help.

[varun_b]

after doing above changes still out is same....

[varun_b]

pmanjunath or nikhilthampi ,
can you at least let me know if this is possible or not in telend? cause Im not getting any doc or solutions

[tchandu]

Hello @varun_b ,

          You can not filter directly on output in tmap. You have to use few variables. I did sample talend job. Hope it will help you to relate with your issue. I have used files to join. Please see the attachment for talend job and source files.

Exports.zip

[varun_b]

Hi 

Thanks for you help, its really helpful.

I tried the same with a actual data for one emp, its giving me duplicate rows. can you please check and let me know what is the mistake Im doing?? I have attached the job and the sample file.

Please help me!!!

Regards,

Varun

sample.zip

[tchandu]

Hello @varun_b ,

         Are you referring to below result,

 

This is not duplicate, because it is for different transaction id. See the below screenshot.

This output is for last two records in file1.txt those are

833|2019-12-27|12806872
833|2020-01-08|12840366