Set vFM = 10 ;
Load
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual Fiscal year
Dual(Month, fMonth) as FMonth, // Dual Fiscal month
*;
Load
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric Fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric Fiscal month
*;
load
Month(CanonicalDate) as Month,
Year(CanonicalDate) as Year,
*;
Load // Your standard master calendar
FileName() as FileName,
*,
[Date 1er contact] as CanonicalDate,
'1er contact' as DateType,
*,
*,
*,
FROM C:\Prosp\*.xlsx (ooxml, embedded labels);
// I separated the loading dates to put all in CanonicalDate
Load [Date Action 1] as CanonicalDate,
'Action 1' as DateType
FROM C:\Prosp\*.xlsx (ooxml, embedded labels);
Load
[Date Action 2] as CanonicalDate,
'Action 2' as DateType
FROM C:\Prosp\*.xlsx (ooxml, embedded labels);
Load
[Date Action 3] as CanonicalDate,
'Action 3' as DateType
FROM C:\Prosp\*.xlsx (ooxml, embedded labels);
Load
[Date Action 4] as CanonicalDate,
'Action 4' as DateType
FROM C:\Prosp\*.xlsx (ooxml, embedded labels);
|