Compare dates in Lookup in Talend

[sushantk19]

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

[manodwhb]

@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

[sushantk19]

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?

[manodwhb]

@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

[sushantk19]

@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

[manodwhb · Accepted solution]

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

[akumar2301]

try tintervalMatch for this

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

[sushantk19]

ok.yes, it is working fine now

[sushantk19]

@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

[sushantk19]

@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?