topic Re: calculate difference between two dates in Talend Studio

calculate difference between two dates

Anonymous — Sat, 16 Nov 2024 10:53:57 GMT

hi,
I am very new to Talend s/w. I have to create a job that calculates the difference between two dates and store the result as 0 & 1 calculated column
e.g. lets say we have a date column
date:                  derived_Col
12/21/2015          0(since it is the first date)
12/21/2014          0(when calculate 12/21/2015-12/21/2014=0 so it is 0)
12/21/2013          0(when calculate 12/21/2014-12/21/2013=0 so it is 0)
11/21/2014          1(when calculate 12/21/2015-12/21/2014=1 so it is 1)
so I have to create a job that calculate the days difference and if the difference is greater than 365days than it should flag as 1 else 0.
Any urgent help on this will be highly appreciated.
Thanks in advance
Anurag Rai

Re: calculate difference between two dates

Anonymous — Mon, 21 Dec 2015 08:23:31 GMT

Hi
I don't understand the logic here, use the current day minus the input data? If so, why it is 'when calculate 12/21/2014-12/21/2013=0 so it is 0' for the 3rd row? Can you please explain it?

Re: calculate difference between two dates

Anonymous — Tue, 22 Dec 2015 09:23:19 GMT

hi shong,
In this requirement we have to calculate difference between two consecutive dates(CurrDate-PrevDate) and if the difference is more than 365 days than it should flag as 1 ELSE 0.
The column is sorted on dates
Hope this helps, if you feel something is missing kindly let me know.
And thanks for the input

Re: calculate difference between two dates

Anonymous — Wed, 23 Dec 2015 04:11:29 GMT

Hi
Take a look at the tMemorizedRow component, this component memorizes the recorded data so you can access them in the next rows. To calculate the difference between two dates, use the built-in function TalendDate.diffDate(Date date1, Date date2, String dateType), you can read the source code of this function by expanding Repository--> Code--> Routines--> System--> TalendDate.
Let me know if you have any troubles to get it works.

Re: calculate difference between two dates

Anonymous — Thu, 24 Dec 2015 09:16:40 GMT

hi Shong,
Thanks for the reply.
I have implemented this already I used tJavaFlex to write a conditional expression to calculate the difference of two dates and store the return value in cnt variable which is already defined as integer but I am keep on getting Type mismatch: cannot convert from int to Long : I searched for this on forum bt I didn't get any definite answer on this.
And one more thing can we create a lead functionality using the tMemorizedRow component in the similar way we are creating a lag function?
tJavaFlex Expression used :
if(!DRUG_ID_tMemorizeRows_1.equals(DRUG_ID_tMemorizeRows_1))
{
context.cnt=1;}
else
{
context.cnt=(TalendDate.diffDate(TalendDate.parseDate("MM/dd/yy", "RX_FILL_DATE_tMemorizeRows_1"),TalendDate.parseDate("MM/dd/yy","RX_FILL_DATE_tMemorizeRows_1"),"dd"));}
Thanks,
Anurag Rai

Re: calculate difference between two dates

Anonymous — Fri, 25 Dec 2015 07:19:04 GMT

Hi
The error occurs on this line:
context.cnt=1;
The data type of context.cnt variable is long, you can't assign an int value to it, the code should be changed to:
context.cnt=1l;