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:
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.
