topic Re: Compare dates in Lookup in Talend in Talend Studio

Compare dates in Lookup in Talend

sushantk19 — Sat, 16 Nov 2024 02:11:08 GMT

Hi,

I have the below scenario. I get a date from my source system in this format : 2020-05-08(yyyy-mm-dd).

I have to compare this date with 2 dates in my lookup table and check it falls within which range i.e

valid_from valid_to

1.2000-01-01 00:00:00 and 2020-07-01 00:00:00, then pass 19
2.2020-07-01 00:00:00 and 2020-12-31 23:59:59, then pass 16
3.2020-12-31 23:59:59 and 9999-12-31 23:59:59, then pass 19

the query here is how do i compare my incoming date (from tmap) with the dates in this lookup table something like incomingdate > valid_from and incomingdate < valid_to

Re: Compare dates in Lookup in Talend

manodwhb — Wed, 10 Jun 2020 13:17:56 GMT

@sushantk19 , you need to compare with belowway.

TalendDate.compareDate(row1.date1,row2.date2)==0

0 means both dates are equal

>0 means row1.date1 greater than row2.date2

<0 means row1.date1 less than row2.date2

Re: Compare dates in Lookup in Talend

sushantk19 — Wed, 10 Jun 2020 13:22:53 GMT

where do i write this logic? in lookup, how to write this? or I have to create a var port and write this logic. Can you share a sample screenshot?

Re: Compare dates in Lookup in Talend

manodwhb — Wed, 10 Jun 2020 13:26:54 GMT

@sushantk19 , you need to write in tmap.chekc the below link ,you will find a screenshot of job.

https://community.talend.com/t5/Design-and-Development/Compare-Dates/td-p/111122

Re: Compare dates in Lookup in Talend

sushantk19 — Wed, 10 Jun 2020 13:55:17 GMT

@manodwhb : Actually My scenario is different. It has nothing to do with Todays date. It has to check the date range in my Lookup table and pass the VAT value accordingly. Please see the screenshot of my job(not working). Please suggest where and how should i do this comparison?

date_comparsion.png

Re: Compare dates in Lookup in Talend

manodwhb — Wed, 10 Jun 2020 14:29:19 GMT

@sushantk19 , you do lookup on country_id and brand_id and compare in Var section for dates.

Re: Compare dates in Lookup in Talend

akumar2301 — Wed, 10 Jun 2020 15:13:17 GMT

try tintervalMatch for this

https://help.talend.com/reader/mjoDghHoMPI0yuyZ83a13Q/XQ89dX5mUG9oJaF5qE0Uew

Re: Compare dates in Lookup in Talend

sushantk19 — Fri, 12 Jun 2020 16:58:46 GMT

ok.yes, it is working fine now

Re: Compare dates in Lookup in Talend

sushantk19 — Thu, 02 Jul 2020 15:45:48 GMT

@manodwhb : sorry for raising this issue again, but my logic is failing for one of the below scenarios. Please find attached my tmap design.

My completed_date is less than todays date, so it should take up vat rate as 19%,but is returning NULL. Also, when the completed_date = todays date i.e 1st july, it should return 16%, but it is not doing so. can you tell me where the issue is?

tmap_date_comparison_error.png

Re: Compare dates in Lookup in Talend

sushantk19 — Thu, 02 Jul 2020 15:45:48 GMT

@manodwhb : sorry for raising this issue again, but my logic is failing for one of the below scenarios. Please find attached my tmap design.

Re: Compare dates in Lookup in Talend

sushantk19 — Thu, 02 Jul 2020 15:47:05 GMT

@uganesh : can you sugggest if i can make modifications in existing code rather than a new logic. Only date comparison part is not working in lookup. I have attached my tmap screenshot for your reference.

tmap_date_comparison_error.png

Re: Compare dates in Lookup in Talend

sushantk19 — Thu, 02 Jul 2020 15:47:05 GMT