emp:
Load * inline
[
empid,empdate
1,7/1/2016
2,7/2/2016
3,7/3/2016
4,7/4/2016
5,7/5/2016
6,7/6/2016
7,7/7/2016
8,7/8/2016
9,7/9/2016
10,7/10/2016
11,7/11/2016
12,7/12/2016
13,7/13/2016
14,7/14/2016
15,7/15/2016
];
bal:
load * inline
[
empid,baldate
4,7/4/2016
5,7/5/2016
7,7/7/2016
8,7/8/2016
9,7/9/2016
12,7/12/2016
13,7/13/2016
15,7/15/2016
];
Map_EmpDate:
Mapping Load empid, empdate Resident emp;
Map_BalDate:
Mapping Load empid,baldate Resident bal;
BridgeTable:
Load empid, ApplyMap('Map_EmpDate', empid, Null()) as CanonicalDate, 'EMP' as Type Resident emp;
Load empid, ApplyMap('Map_BalDate', empid, Null()) as CanonicalDate, 'BAL' as Type Resident bal;
MasterCalendar:
Load
Date(TempDate) as CanonicalDate,
Month(TempDate) as CanonicalMonth,
Year(TempDate) as CanonicalYear
;
Load
MinDate + IterNo() - 1 as TempDate
While MinDate + IterNo() - 1 <= MaxDate;
Load
Min(FieldValue('CanonicalDate',RecNo())) as MinDate,
Max(FieldValue('CanonicalDate',RecNo())) as MaxDate
AutoGenerate FieldValueCount('CanonicalDate');
