Calculating Age Based on Date of Birth

[Manmeet]

Hi All,
I need to calculate the age of the Employees as on today based on "Date of Birth" column which is available in the Data.

Looking forward for Solution suggestions .

Thanks & Regards,

Manmeet Dhawan

[sidhiq91]

@Manmeet  Please see below and let me know if it resolved your issue.

First I created a sample Inline table, in your case it will be your data:

NoConcatenate
Temp:
Load * inline [
Name, DOB,
Sidhiq, 09/06/1991,
Satish, 14/08/1990,
Danish, 31/03/1991,
Avinash, 22/11/1989,
];

Expression to Calculate Age:

=Year(Date(Today(),'DD/MM/YYYY'))-Year(Date#(DOB,'DD/MM/YYYY'))

[sidhiq91]

@Manmeet  You can simple use Age Function as well. Just like below. 

=Age(date(Date#(today(),'MM/DD/YYYY'),'MM/DD/YYYY'),Date(Date#(DOB,'DD/MM/YYYY'),'MM/DD/YYYY'))

Please let me know if it has resolved your issue.

[Manmeet]

Hey thanks for your response , It worked i guess.. not sure if the values are correct or not , That i need to validate with the stakeholder but it only works when i add the above formula in the expression editor . I need to make it a column , So when I'm adding this in Data load editor and give alias as AGE...In front end there are no Values in the AGE column.

Can you suggest ?

[Manmeet]

Hi, Thanks For your response 
Using this expression I'm getting the values but not sure whether it is correct or not, 

That i need to validate with the stakeholder but it only works when i add the above formula in the expression editor . I need to make it a column , So when I'm adding this in Data load editor and give alias as AGE...In front end there are no Values in the AGE column.  As using this AGE Column i need to create age bands like: 25-30, 30-40,40-50.

Something like that..

Can you suggest ?

[sidhiq91]

@Manmeet  I have created your requirement in the Script Editor. If you follow the same code, you should get the desired result. 

NoConcatenate
Temp:
Load *,
if(Age<'25','Below 25',
if(Age>=25 and Age<=30,'25-30',
if(Age>30 and Age<=40,'31-40',
if(Age>40 and Age<=50,'41-50','Above 50')))) as Age_Bucket;

Load *,
Age(date(Date#(today(),'MM/DD/YYYY'),'MM/DD/YYYY'),Date(Date#(DOB,'DD/MM/YYYY'),'MM/DD/YYYY')) as Age;

Load * inline [
Name, DOB
Sidhiq, 09/06/1991
Satish, 14/08/1990
Danish, 31/03/1991
Avinash, 22/11/1989
Binu, 22/11/2000
Syed, 21/05/1968
Sabina, 02/08/1975
];

Exit Script;

Please like and accept as solution if it has solved your issue.

[Manmeet]

Hi Thanks for the response, This is also not working actually... getting error in Calender table saying DOB not found.
Can you attach screenshots of how you are implementing here... Thanks you

[sidhiq91]

@Manmeet  I just followed the above code in script Editor. But if you can send me your script and data, I will let u know if there are any changes that you need to make. 

[Manmeet]

Yes sure...thanks , I think it is working fine now , only thing is that this script is only giving the exact numbers, it has to be Age as on today. example: 29.5, 32.2 like that . I'm using the below script , let me know how i can fix this issue.

Thank you

NoConcatenate
Temp:
Load *,
if(Age<'25','Less than 25',
if(Age>=25 and Age<=30,'25-30',
if(Age>30 and Age<=35,'30-45',
if(Age>35 and Age<=40,'35-40',
if(Age>40 and Age<=45,'40-45',
if(Age>45 and Age<=50,'45-50',
if(Age=' ','NULL','More than 50'))))))) as Age_Bucket;

Load *,
//Year(Date(Today(),'MM/DD/YYYY'))-Year(Date#(DOB,'MM/DD/YYYY')) as Age;
Age(date(Date#(today(),'MM/DD/YYYY'),'MM/DD/YYYY'),Date(Date#(DOB,'MM/DD/YYYY'),'MM/DD/YYYY')) as Age;

[sidhiq91]

@Manmeet  Below Script should fulfill all your requirements. Kindly give a like and accept it as your solution if it has resolved your issue.

NoConcatenate
Temp:
Load *,
if(Age<'25','Below 25',
if(Age>=25 and Age<=30,'25-30',
if(Age>30 and Age<=40,'31-40',
if(Age>40 and Age<=50,'41-50','Above 50')))) as Age_Bucket;

Load *,

Num((Interval(date(Date#(today(),'MM/DD/YYYY'),'MM/DD/YYYY')
-Date(Date#(DOB,'DD/MM/YYYY'),'MM/DD/YYYY'),'DD')/365),'#,##.00')
as Age;
;

Load * inline [
Name, DOB
Sidhiq, 09/06/1991
Satish, 14/08/1990
Danish, 31/03/1991
Avinash, 22/11/1989
Binu, 22/11/2000
Syed, 21/05/1968
Sabina, 02/08/1975
];

Exit Script;