calculate difference between two dates

One Star

calculate difference between two dates

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
Community Manager

Re: calculate difference between two dates

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?
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: calculate difference between two dates

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
Community Manager

Re: calculate difference between two dates

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.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: calculate difference between two dates

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
Community Manager

Re: calculate difference between two dates

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;
----------------------------------------------------------
Talend | Data Agility for Modern Business