Get Age in Months

[tchovanec]

I am trying to get the age in months for someone for every month. So I created a table that holds the month end for every month from 2009 - 2013. You will see how I create this below.

LET vMinDate = NUM(DATE('1/1/2009'));
 LET vMaxDate = NUM(DATE(TODAY()));
 
 TempCalendar:
 LOAD
      $(vMinDate) + RowNo() - 1 AS DateNumber
 AUTOGENERATE 1
 WHILE $(vMinDate) + IterNo()-1 <= $(vMaxDate);
 
 MonthEnd:
 Load
      DateNumber
      ,Floor(MonthEnd(DateNumber))as MonthEnd
      ,Floor(MonthStart(DateNumber)) as MonthStart
 RESIDENT TempCalendar;

I also have a table that holds the date of birth for an individual. I am trying to create a table that will hold the DOB, MonthEnd, MonthStart and the Age at that at that time. So the table will look like something below.

PatientNumber	DOB	MonthStart	MonthEnd	AgeInMonths
1	12/01/2008	01/01/2009	01/31/2009	1
1	12/01/2008	02/01/2009	2/28/2009	2
1	12/01/2008	03/01/2013	03/31/2013	51

Any help would be greatly appreciated. Thank you.

[jagan · Accepted solution]

Hi,

Please find below link

http://community.qlik.com/docs/DOC-3836

Hope this helps you.

Regards,

Jagan.

[tchovanec]

Jagan, 

This will work great once I get the data into one table. I am struggling with getting the data into one table. Below are examples of the two tables I have.

PatientNumber	DOB
1	01/01/2010
2	12/10/2008
3	08/11/2009
4	07/01/2011
5	06/30/2012

Date	MonthEnd
01/01/2010	01/31/2010
01/02/2010	01/31/2010
01/03/2010	01/31/2010

Here is the final table I am looking at trying to get.

PatientNumber	DOB	MonthEnd
1	01/01/2010	01/31/2010
1	01/01/2010	02/28/2010
1	01/01/2010	03/31/2010
1	01/01/2010	04/30/2010
2	12/10/2008	01/01/2009
2	12/10/2008	02/28/2009
2	12/10/2008	03/31/2009
3	08/11/2009	09/09/2009

So for every patient number and DOB there will be 12 records a year. Thanks again for the help.