topic Re: left join with date conditions in Talend Studio

left join with date conditions

varun_b — Wed, 15 Jan 2020 16:20:47 GMT

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

Re: left join with date conditions

Anonymous — Wed, 15 Jan 2020 18:00:45 GMT

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

Re: left join with date conditions

varun_b — Thu, 16 Jan 2020 09:39:46 GMT

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

Re: left join with date conditions

Anonymous — Thu, 16 Jan 2020 10:28:05 GMT

Hi @varun_b ,

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

Re: left join with date conditions

varun_b — Thu, 16 Jan 2020 11:00:43 GMT

Hi ,

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

Marked in yellow is my condition.

kindly help.

Re: left join with date conditions

varun_b — Thu, 16 Jan 2020 12:31:08 GMT

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

Re: left join with date conditions

varun_b — Fri, 17 Jan 2020 04:51:57 GMT

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

Re: left join with date conditions

tchandu — Fri, 17 Jan 2020 10:23:56 GMT

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

Re: left join with date conditions

varun_b — Mon, 20 Jan 2020 12:42:47 GMT

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

Re: left join with date conditions

tchandu — Wed, 22 Jan 2020 01:20:11 GMT

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