Good day All, I have two calendars which I intend on merging into a master calendar so that I can avoid having synthentic keys in my data model. The first calendar has the hire date of an employee and the second calendar has the salesdate of each employee and I initially looked at this articleCanonical Datebut there is no fine grain table between the two calendars so I was wondering whether there might be an alternative to doing this.
Here is my code below I cut it down to make things alot easier to read:
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);
Employee_Cal:
LOAD Date([Hire date]) as [Hire date],
Year([Hire date]) as Year,
'Q' & Ceil(Month([Hire date])/3) as Quarter,
Month([Hire date]) as Month,
Day([Hire date]) as Day,
Week([Hire date]) as Week;
LOAD Date(MinDate + Iterno() -1) as [Hire date]
While(MinDate + Iterno() -1)<=Num(MaxDate);
LOAD Min([Hire date]) as MinDate,
Max([Hire date]) as MaxDate
Resident Employee;
Sales:
LOAD [Sales Date],
[Employee ID],
[Sales Amount]
FROM
(ooxml, embedded labels, table is Sales);
Sales_Cal:
LOAD Date([Sales Date]) as [Sales Date],
Year([Sales Date]) as Year,
'Q' & Ceil(Month([Sales Date])/3) as Quarter,
Month([Sales Date]) as Month,
Day([Sales Date]) as Day,
Week([Sales Date]) as Week;
LOAD Date(MinDate + Iterno() -1) as [Sales Date]
While(MinDate + Iterno() -1)<=Num(MaxDate);
LOAD Min([Sales Date]) as MinDate,
Max([Sales Date]) as MaxDate
Resident Sales;
Now how do I get the Employee+Cal and Sales_cal into a master calendar in this case?
