Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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!