Rolling hours to Master Calender

Wed, 07 Jul 2010 11:07:00 GMT

I have an master calender that I need to add Rolling hours to but I don't know how to do it.
I have roling days, Weeks Years etc but now I need hours aswell.

Any help is appreciated
I'll add my working Master calender script below

// Generate master calendar

LET vStartDate = MakeDate(2010,7,1);

LET vEndDate = Date(Today());

LET vStartHour = Hour('00:00:00');

LET vEndHour = Hour('23:00:00');

SET vSEP = &'_-_'&;

Date:

Load

// Keys

Date as %Date,

Date as %DayCounter,

MonthStart(Date) as %MonthCounter,

YearStart(Date) as %YearCounter,

QuarterStart(Date) as %QuarterCounter,

// Date Attributes

Date as Date,

Year(Date) as Year,

Dual('Q' & Ceil(Num(Month(Date))/3), Ceil(Month(Date)/3)) as [Quarter],

Month(Date) as Month,

Week(Date) as Week,

Day(Date) as Day,

Year(Date) & Week(Date) as YearWeek,

Date(MonthStart(Date), 'YYYY-MM') as [Period MM.YYYY],

DayNumberOfYear(Date) as _DayCounterYear;

Load

Date($(#vStartDate) +(Iterno()-1)) as Date

Autogenerate(1)

While

$(#vStartDate)+(Iterno()-1) <= $(#vEndDate);

// DAY

// MONTH

tmpDayCounter:

Load distinct

%DayCounter

Resident

Date;

DayCounter:

LEFT JOIN (Date)

Load

%DayCounter,

RowNo() as _DayCounter

Resident

tmpDayCounter

Order by

%DayCounter ASC;

Drop table tmpDayCounter;

// MONTH

tmpMonthCounter:

Load distinct

%MonthCounter

Resident

Date;

MonthCounter:

LEFT JOIN (Date)

Load

%MonthCounter,

RowNo() as _MonthCounter

Resident

tmpMonthCounter

Order by

%MonthCounter ASC;

Drop table tmpMonthCounter;

// QUARTER

tmpQuarterCounter:

Load distinct

%QuarterCounter

Resident

Date;

QuarterCounter:

LEFT JOIN (Date)

Load

%QuarterCounter,

RowNo() as _QuarterCounter

Resident

tmpQuarterCounter

Order by

%QuarterCounter ASC;

Drop table tmpQuarterCounter;

// YEAR

tmpYearCounter:

Load distinct

%YearCounter

Resident

Date;

YearCounter:

LEFT JOIN (Date)

Load

%YearCounter,

RowNo() as _YearCounter

Resident

tmpYearCounter

Order by

%YearCounter ASC;

Drop table tmpYearCounter;

Rolling hours to Master Calender

Wed, 07 Jul 2010 13:42:51 GMT

Here it is:

Date:
Load
// Keys
Date as %Date,
Date as %DayCounter,
MonthStart(Date) as %MonthCounter,
YearStart(Date) as %YearCounter,
QuarterStart(Date) as %QuarterCounter,
// Date Attributes
Date as Date,
Year(Date) as Year,
Dual('Q' & Ceil(Num(Month(Date))/3), Ceil(Month(Date)/3)) as [Quarter],
Month(Date) as Month,
Week(Date) as Week,
Day(Date) as Day,
Year(Date) & Week(Date) as YearWeek,
Date(MonthStart(Date), 'YYYY-MM') as [Period MM.YYYY],
DayNumberOfYear(Date) as _DayCounterYear,
DateHour
;
Load
DateHour,
date(floor(DateHour)) as Date
;
Load
date((($(#vStartDate) * 24) +(Iterno()-1))/24) as DateHour
Autogenerate(1)
While
($(#vStartDate) * 24) +(Iterno()-1) <= $(#vEndDate) * 24;

Rolling hours to Master Calender

Thu, 08 Jul 2010 12:06:21 GMT

Thanx for the info, It work perfect !

topic Rolling hours to Master Calender in QlikView

Rolling hours to Master Calender

Rolling hours to Master Calender

Rolling hours to Master Calender