topic Re: Master calender with hours in QlikView

Master calender with hours

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Master calender with hours

jonathandienst — Wed, 16 May 2018 04:58:34 GMT

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

Re: Master calender with hours

ashis — Wed, 16 May 2018 05:32:17 GMT

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;

Re: Master calender with hours

jonathandienst — Wed, 16 May 2018 06:24:01 GMT

>>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.

Re: Master calender with hours

jonathandienst — Wed, 16 May 2018 06:28:14 GMT

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.

Re: Master calender with hours

ashis — Wed, 16 May 2018 11:13:53 GMT

Thank you Jonathan.