Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate Age in load over 2 tables

Hello,

I try to load 2 tables in my QV document. Both are Excel files. In the first table I load dates when a customer starts and ends a subscription with us. Example:

SubscriptionCustomer_IdStartDateEndDate
11001-01-201531-10-2015
2601-05-201430-04-2015
3801-08-201431-08-2014

In the second table i load the birthdate of the customer: Example:

Customer_IdNameDate_of_Birth
6Jan05-05-2001
8John21-07-1963
10Sanne16-11-1988

Now i want to load in one of the tables the age of the customer when he started his subscription. This is easier in the rest of the document so i don't need to calculate the age in every chart i will use it.

I hope somebody can help me with this!

Thanks in advance.

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

Table2:

Load Customer_Id, Name, Date_of_Birth

from Table2.xls

[....];

Map_DOB:

Mapping Load

Customer_Id, Date_of_Birth

resident Table2;

Table1:

load Subscription, Customer_Id, StartDate, EndDate, Year(Date#(StartDate,'DD-MM-YYYY') - Year(Date#(Applymap('Map_DOB',Customer_Id,null()),'DD-MM-YYYY') as AgeAtStart

from Table1.xls

[....];

View solution in original post

3 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Left join the 2 tables, in this way you will have the date of Birth of each customes in any row of subscriptios table.

Now reload the table (Ex. MyTable) and use the age function:

FinalTable:

Load

...

age(start-date, date_of_birth)

...

Resident MyTable;

drop table myTable;

Let me know

simenkg
Specialist
Specialist

Table2:

Load Customer_Id, Name, Date_of_Birth

from Table2.xls

[....];

Map_DOB:

Mapping Load

Customer_Id, Date_of_Birth

resident Table2;

Table1:

load Subscription, Customer_Id, StartDate, EndDate, Year(Date#(StartDate,'DD-MM-YYYY') - Year(Date#(Applymap('Map_DOB',Customer_Id,null()),'DD-MM-YYYY') as AgeAtStart

from Table1.xls

[....];

Not applicable
Author

Thanks Simen,

I do calculate the age with the Age() function! But the Mapping Load function works! Thank you very much!