Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Canonical Date Problem

Good day All, I posted a similar question yesterday but didn't get a solution to it what i want to do is to link two tables with dates into a master calendar. So the first table Employee has the hire date and the second table Sales has the sales date but the dates in both tables have different start and end dates. The canonical master table I created does not seem to be working currently so not sure what I might be missing.

See my code below:

See my code below:


LOAD [Employee ID],

     [First name],

     [Last name],

     [Hire date],

     [Temp worker],

     [Position ID],

     [Department ID],

     [Office ID],

     [Manager ID]


(ooxml, embedded labels, table is Employee);


LOAD [Sales Date],

     [Employee ID],

     [Sales Amount]


(ooxml, embedded labels, table is Sales);


     Load [Employee ID], Applymap('[Employee ID]2[Hire Date]',[Employee ID],Null()) as CanonicalDate, 'Employee' as DateType

     Resident Employee;


     Load [Employee ID], Applymap('[Employee ID]2[Sales Date]',[Employee ID],Null()) as CanonicalDate, 'Sales' as DateType

     Resident Sales;



LOAD Date([CanonicalDate]) as CanonicalDate,

     Year(CanonicalDate) as Year,

     'Q' & Ceil(Month(CanonicalDate)/3) as Quarter,

     Month(CanonicalDate) as Month,

     Day(CanonicalDate) as Day,

     Week(CanonicalDate) as Week;

LOAD Date(MinDate + Iterno() -1) as CanonicalDate

     While(MinDate + Iterno() -1)<=Num(MaxDate);

LOAD Min(CanonicalDate) as MinDate,

     Max(CanonicalDate) as MaxDate

Resident DateBridge;

I attached the structure of both databases as well.

Thanks in advance.

0 Replies