Canonical Date Problem

kaygee28 — Mon, 07 May 2018 09:29:01 GMT

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:

Employee:

LOAD [Employee ID],

[First name],

[Last name],

[Hire date],

[Temp worker],

[Position ID],

[Department ID],

[Office ID],

[Manager ID]

FROM

(ooxml, embedded labels, table is Employee);

Sales:

LOAD [Sales Date],

[Employee ID],

[Sales Amount]

FROM

(ooxml, embedded labels, table is Sales);

DateBridge:

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;

Master_Calendar:

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.

topic Canonical Date Problem in QlikView

Canonical Date Problem