多数情况下DW会给报表输出一张比较全的标准日历表,如果是这样的情况,那么可以直接从DW中读取,所以我要写的是DW中没有提供并且从事实数据中提取并不完整或者太耗资源的情况,其实是可以通过脚本自动生成一份标准日历表,下面就介绍一下思路。
//定义变量
LET vDateMin = Num(MakeDate(2015,1,1)); //起始时间2015-01-01
LET vDateMax = Floor(MonthEnd(Today())); //结束时间为今天所在月最后一天
LET vDateToday = Num(Today()); //今天变量
//定义循环
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
//加载日历表
Calendar:
LOAD
Date(TempDate) AS 标准日期,
AutoNumber(Year(TempDate)&Month(TempDate)&Day(TempDate)) as 时间KEY,
// Standard Date Objects
Day(TempDate) AS 标准日份,
WeekDay(TempDate) AS 标准星期,
Week(TempDate) AS 标准周数,
Month(TempDate) AS 标准月份,
'Q' & Ceil(Month(TempDate)/3) AS 标准季度,
Year(TempDate) AS 标准年份
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
//释放变量
LET vDateMin = Null();
LET vDateMax = Null();
LET vDateToday = Null();