Hi,
I have excel file with four sheets. There are Date column in those four sheets. I need to consider those four sheets Dates into master calendar? will the below script work to create master calendar?
TRACE Creating Master Calendar;
MinMax:
LOAD
Max(TIMESTAMP([Date])) AS MaxDate,
Min(TIMESTAMP([Date])) AS MinDate
FROM
A.xlsx
(ooxml, embedded labels, table is DataA);
Join
LOAD
Max(TIMESTAMP([Date])) AS MaxDate,
Min(TIMESTAMP([Date])) AS MinDate
FROM
B.xlsx
(ooxml, embedded labels, table is DataB);
Join
LOAD Max(TIMESTAMP([Date])) AS MaxDate,
Min(TIMESTAMP([Date])) AS MinDate
FROM
C.xlsx
(ooxml, embedded labels, table is DataC);
Join
LOAD Max(TIMESTAMP([Date])) AS MaxDate,
Min(TIMESTAMP([Date])) AS MinDate
FROM
D.xlsx
(ooxml, embedded labels, table is DataD);
//EXIT SCRIPT;
/* STORE earliest and latest dates in variables */
LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax'));
// EXIT SCRIPT;
/* STORE todays date in variable */
LET varToday = Num(Today());
/* Inline TABLE mapping months to quarters */
QuarterMap:
MAPPING LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];
/* Autogenerate every date between varMinDate and varMaxDate while creating calendar fields */
MasterCalendar:
LOAD*,
FiscalYear & '/ W' & FiscalWeek AS FiscalYearWeek;
LOAD*,
YearStart(Date,0,7) AS FiscalYearStart,
YearName(Date,0,7) AS FiscalYear,
AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,
ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,
'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;
LOAD
//DATE(FLOOR(Date),'DD/MM/YYYY') AS Date,
Timestamp(Date) as Date_TS,
Date#(Date(Date)) AS Date,
IF(Date = Today(),1) AS Today,
week(Date) AS Week,
Year(Date) AS Year,
Month(Date) AS Month,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay,
ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,
Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,
Week(Date) & '-' & Year(Date) AS WeekYear,
InYearToDate(Date, $(varToday), 0) * -1 AS CurYTDFlag,
InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;
LOAD
Date($(varMinDate) + RecNo() - 1) AS Date
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);
/* DROP Temporary TABLE */
DROP TABLE MinMax;
STORE MasterCalendar INTO C:MasterCalendar.qvd;
DROP TABLE MasterCalendar;
Thanks.
