Master calender with hours

[ashis]

Hi ,

I want to create master calendar with hour. My script looks like this

MasterCalender: 
LOAD 
Date,
  Year(Date) AS Year, 
'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,
Date(MonthStart(Date),'MMM') as Month1,
Date(MonthStart(Date),'MMM-YYYY') as MonthYear,
Year(Date) * 12 + num(Month(Date)) as [Month Counter],
Day(Date) As Day, 
WeekDay(Date) AS WeekDays,
Hour(Date) as Hour,
Week(Date) As Week; 
Load 
Date(MinDate + IterNo() -1 ) AS Date

While (MinDate + IterNo() - 1) <= Num(MaxDate); 
Load 
Min(Start_Time) AS MinDate, 
Max(End_Time) AS MaxDate  

RESIDENT acb; 

Start_Time and End_Time have date format like 'DD-MM-YYYY hh:mm:ss:ttt'  .

The hour field in above only returning 22 .

Could you please help.

[jonathandienst]

First of all, you are only getting 22 for the hour as you iterating in days, but not hours - this must be the time value from fractional part of Start_Time.

You can create an Hour field by outer-joining to the normal day calendar

Join(MasterCalendar)

LOAD Time((Hour-1)/24, 'h') As Hour

AutoGenerate 24;

MasterCalendarFinal:

LOAD *,

    Date(Floor(Date)) as DateWhole,

    TimeStamp(Floor(Date) + Hour, 'DD-MM-YYYY hh') as Date
Resident MasterCalendar

DROP Table MasterCalendar;

If your fact table date/time values are not rounded to hours, you will need to do so yourself to associate with the Date value. When loading:

    LOAD

          ...

          TiimeStamp(Round(TransactionDate, 1/24), 'DD-MM-YYYY hh') as Date,

          ...

But have a look here: The Importance Of Being Distinct

[ashis]

Hi Jonathan,

I am little confused in one place.

You said "You can create an Hour field by outer-joining to the normal day calendar"

is that mean I need to create an hour column  from the source table like

Time(Floor(Frac(Start_Time),1/24),'hh:mm') as hours

The following is the source QVD

abc,

xxy,

date(Start_Time,'DD-MM-YYYY hh:mm:ss:fff') as Start_Time,
date(End_Time,'DD-MM-YYYY hh:mm:ss:fff') as End_Time,
Time(Floor(Frac(Start_Time),1/24),'hh:mm') as hours,
Time(Round(Frac(Start_Time),1/24/60),'hh:mm') as Time_data,
DATE(FLOOR(Start_Time), 'DD-MMM-YY') as Date

from QVD;

My master calendar looks like this

MasterCalender: 
LOAD 
Date(Floor(Date)) as Date,

Year(Date) AS Year, 
'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,
Date(MonthStart(Date),'MMM') as Month1,
Date(MonthStart(Date),'MMM-YYYY') as MonthYear,
Year(Date) * 12 + num(Month(Date)) as [Month Counter],
Day(Date) As Day, 
WeekDay(Date) AS WeekDays,
//hour(Date) as Hour,
   Week(Date) As Week;

Load 
Date(MinDate + IterNo() -1 ) AS Date

While (MinDate + IterNo() - 1) <= Num(MaxDate); 
Load 
Min(Start_Time) AS MinDate, 
Max(End_Time) AS MaxDate  

RESIDENT xxx; 

Join(MasterCalendar)

LOAD Time((Hour-1)/24, 'h') As Hour

AutoGenerate 24;

[jonathandienst]

>>is that mean I need to create an hour column  from the source table

No. You would associate the fact table and calendar through the Date field (which contains a fractional part of hours). So you need to ensure that the fact table Date field is also rounded to hours.

The Hours field will be useful to get the time of day.

The script that you have posted is incomplete. Please check the second part of my earlier post.

[jonathandienst]

Here the full script that you need

T_CAL:

Load 

     Date(MinDate + IterNo() -1 ) AS T_Date

While (MinDate + IterNo() - 1) <= Num(MaxDate); 

Load 

     Min(Floor(Start_Time)) AS MinDate, 

     Max(Floor(End_Time)) AS MaxDate  

RESIDENT xxx;

Join(T_CAL)   // this will be cartesian (cross) join

LOAD Time((Hour-1)/24, 'h') As Hour

AutoGenerate 24;

MasterCalender: 

LOAD 

     TimeStamp(T_Date+Hour, 'DD-MM-YYYY hh') as Date,

     Date(T_Date, 'DD-MM-YYYY') as Date_Whole,

     Year(T_Date) AS Year, 

     'Q' & Ceil(Month(T_Date) / 3) AS Quarter,

     Month(T_Date) As Month,

     Date(MonthStart(T_Date),'MMM-YYYY') as MonthYear,

     Year(T_Date) * 12 + Month(T_Date) as [Month Counter],

     Day(T_Date) As Day, 

     WeekDay(T_Date) AS WeekDay,

     Week(T_Date) As Week

     Hour

Resident T_CAL;

DROP Table T_CAL;

Then the Fact table load to associate needs a Date field like:

...

TimeStamp(Round(Start_Time, 1/24), 'DD-MMM-YY') as Date,

...

And you can use Date_Whole as a dimension for complete days if you need it.

[ashis]

Thank you Jonathan.