Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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
Partner
Partner

Re: Calculate Age in load over 2 tables

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

Re: Calculate Age in load over 2 tables

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

Partner
Partner

Re: Calculate Age in load over 2 tables

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

Not applicable

Re: Calculate Age in load over 2 tables

Thanks Simen,

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