How to implement hours within master calendar script ?

[lessassy]

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

[sunny_talwar]

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

What error message do you get?

[lessassy]

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

[marcus_sommer]

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

[lessassy]

It worked fine thank you a lot !