Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi ,
Yep, I have selected All matches only. I have attached actual tmap screenshot.
Marked in yellow is my condition.
kindly help.
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.
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
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