One Star

fill empty age column with calculation from date of birth

Hi, i am new using JasperETL and i need some help.
My problem is i need to fill some data in Age column with calculation of DateOfBirth
here is the table:
Name DateOfBirth Age
Andi 06/12/2001 10
Agus 07/11/2001 (empty)
Baba 02/14/2005 (empty)
can someone help me to guide me how to solve this problem in JasperETL.
Thanks.
11 REPLIES
Community Manager

Re: fill empty age column with calculation from date of birth

Hi
Using the build-in function diffDate(Date date1, Date date2), for example:
tMysqlInput--row1--tMap--tLogRow
Assuming the data type of Age column is String, set the expression field of Age column on outuput table as below:
row1.age==null? (TalendDate.diffDate(TalendDate.getCurrentDate(), row1.DateOfBirth,"yyyy")).toString():row1.age
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: fill empty age column with calculation from date of birth

i'll try it. thanks for the help! you're very helpful..
Seven Stars

Re: fill empty age column with calculation from date of birth

Don't use diffDate for Age calculations as it simply compares the year of the two dates and ignores the months and days i.e. diffDate(2009/05/20, 2008/10/15, "yyyy") returns 1. Use diffDateFloor instead.
One Star

Re: fill empty age column with calculation from date of birth

Hi
Using the build-in function diffDate(Date date1, Date date2), for example:
tMysqlInput--row1--tMap--tLogRow
Assuming the data type of Age column is String, set the expression field of Age column on outuput table as below:
row1.age==null? (TalendDate.diffDate(TalendDate.getCurrentDate(), row1.DateOfBirth,"yyyy")).toString():row1.age
Best regards
Shong

sorry, i need one more help.. can you explain the step #2. i've input the tMysqlInput but i don't understand what the meaning of row1? is that a palette or what? and where i can set the expression field??
thanks.
Community Manager

Re: fill empty age column with calculation from date of birth

Hi
row1 is the flow name, it could be row2, row3, rowN, the flow name will display automatically once you link one component to another one. Set the expression on the column of output table. see my screenshots.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: fill empty age column with calculation from date of birth

Hi shong, i am sorry but i need ur help again..
i've done the step that u told above and i had some error in the expression of tMap when i test it..
it says: "Cannot invoke toString() on the primitive type long"
thanks.
Community Manager

Re: fill empty age column with calculation from date of birth

Hi
Sorry, the method returns a long type, change the expression to:
row1.age==null? String.valueOf((TalendDate.diffDate(TalendDate.getCurrentDate(), row1.DateOfBirth,"yyyy"))):row1.age
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: fill empty age column with calculation from date of birth

and now the error is "Type mismatch: cannot convert from Object&Serializable&Comparable<?>to String".
sorry to bother you.
thanks a lot.
Community Manager

Re: fill empty age column with calculation from date of birth

Hi
What's data type of column? Some screenshots of job will be helpful for us understanding the problem.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: fill empty age column with calculation from date of birth

here is the screenshot.
Community Manager

Re: fill empty age column with calculation from date of birth

Hi
Sorry, Where's your pictures? Please try it again.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business