topic Re: How to implement hours within master calendar script ? in QlikView

How to implement hours within master calendar script ?

lessassy — Wed, 09 May 2018 08:57:36 GMT

Hello everyone,

I've been working on Qlkiview for weeks now in my internship.

I recently learnt how to make a master calendar and it works.

But now i kind of want to incorporate data field which incorporate DD/MM/YYYY HH/MM into qlikview from the field below

But when i reload the script i get a message error.

There have not been any specific tuto (from begining to end) on how to include time into master calendar.

The Master Time Table

I read this one but as I'm french, there few things that I may have missed.

In order to work Do i need to create an other excel file with a field Time as well ? Because i only use one excel file

And here is my full script:

Tab 2: Master Calendar:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
               min(Created) as minDate,
               max(Created) as maxDate
Resident MyTable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
               TempDate AS Created,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               YeartoDate(TempDate)*-1 as CurYTDFlag,
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident MyTable
Order By TempDate ASC;
Drop Table TempCalendar;

Tab 3: Time Calendar:

MinMaxDates:

LOAD Floor(Min(TimeStamp#(Created, 'DDMMMYYY:hh:mm:ss'))) AS MinDate,

Floor(Max(TimeStamp#(Created, 'DDMMMYYY:hh:mm:ss'))) AS MaxDate

RESIDENT MyTable;

LET vMinDate = FieldValue('MinDate', 1);

LET vMaxDate = FieldValue('MaxDate', 1);

DROP TABLE MinMaxDates;

CalendarTemp:

LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate,

TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS AddedTimeStamp

AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

Calendar:LOAD AddedTimeStamp AS Created,

Year(AddedTimeStamp) AS MyYear,

WeekYear(AddedTimeStamp) as WeekYear,

WeekDay(AddedTimeStamp) as WeekDay,

Week(AddedTimeStamp) as Week,

WeekName(AddedTimeStamp) as WeekName,

Month(AddedTimeStamp) as Month,

MonthName(AddedTimeStamp) as MonthName,

Ceil(Month(AddedTimeStamp)/3) as Quarter,

QuarterName(AddedTimeStamp) as QuarterName,

Year(AddedTimeStamp) as Year,

WeekYear(AddedTimeStamp) as WeekYear,

DayName(AddedTimeStamp) as Date,

Hour(AddedTimeStamp) as Hour;

Thank you in advance for your response

Re: How to implement hours within master calendar script ?

sunny_talwar — Wed, 09 May 2018 11:52:13 GMT

But when i reload the script i get a message error.

What error message do you get?

Re: How to implement hours within master calendar script ?

lessassy — Wed, 09 May 2018 12:16:32 GMT

I have this error

But aside my data file (excel), do i have to create another excel file with another date field.

I'm a bit confused

Re: How to implement hours within master calendar script ?

marcus_sommer — Wed, 09 May 2018 14:10:10 GMT

Usually it's not a good idea to extend a master-calendar to a time-part. There is no added value else only disadvantages like counting on days don't work properly and also a longer table-pointer and probably some more, see for this: The Importance Of Being Distinct.

Better will be to use a master-calendar for the dates and a master-timetable for the times - they could be easily with:

date(floor(Created)) as Date,

time(frac(Created)) as Time

separated within the fact-table and linked to the master-tables.

Further quite often it's not necessary to read the min/max dates from the facts because they are usually known - quite probably you know the date from your first fact-record and this won't change and the max. date is often something like today or yesterday or monthend() or yearend() or similar from them. Quite similar by the times because each day has excactly the same number of seconds.

Here could you find many more things which are related to How to use - Master-Calendar and Date-Values.

- Marcus

Re: How to implement hours within master calendar script ?

lessassy — Fri, 18 May 2018 15:02:59 GMT

It worked fine thank you a lot !