Thanks. Where I need to use Order By Clause. Please see below is my script to load master Calendar into QVD
TRACE Creating Master Calendar;
/* Load transaction TABLE to get earliest and latest dates */
MinMax:
LOAD
Max(TIMESTAMP(DateEnter)) AS MaxDate,
Min(TIMESTAMP(DateEnter)) AS MinDate
FROM
Extractor\TblSales.qvd
(qvd);
/* STORE earliest and latest dates in variables */
LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax') +1);
/* 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
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:\Qlikview\MasterCalendar.qvd;
DROP TABLE MasterCalendar;
