topic Re: Master calendar on timestamp in QlikView

Master calendar on timestamp

mcornips — Tue, 15 Sep 2015 19:44:45 GMT

I am trying to generate a Master Calendar based on a time stamp. In the Master Calendar I only need the date and the full hours (the 24 hours of the day) and not the minutes or the seconds.

I was trying the script as mentioned below, but it does not work.

Does anybody have an idea what I should change in the script?

MinMax:

LOAD

Floor (Min (Timestamp#(Systeemtijd, 'YYYY-MM-DD hh'))) as MinDate,

Floor (Max (Timestamp#(Systeemtijd, 'YYYY-MM-DD hh'))) as MaxDate

RESIDENT Metingen;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

TempCal:

LOAD

date (Timestamp($(vMinDate) + (rowno()/24) - 1)) AS TempDate

AUTOGENERATE

[($(vMaxDate) - $(vMinDate) + 1)*24];

DROP TABLE MinMax;

MasterCalendar:

LOAD

//TempDate as IslandDate,

TempDate AS Systeemtijd,

date(makedate(year(TempDate),month(TempDate),day(TempDate)),'YYYY-MM-DD hh') as Datum,

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Date(monthstart(TempDate), 'YYYY-MMM') AS YearMonth,

Year(TempDate)&'-'&Week(TempDate) AS YearWeek,

inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: Master calendar on timestamp

sunny_talwar — Tue, 15 Sep 2015 22:01:22 GMT

Look at this document by jagan‌: Master Calendar with Hour‌.

Alternatively, you can also check this out: The Master Time Table

I hope the above two links will prove helpful.

Best,

Sunny

Re: Master calendar on timestamp

mcornips — Thu, 17 Sep 2015 08:11:20 GMT

Thank you Sunny, this was helpful. I now separated timestap into date and time and I created a master calendar and a master time table.

Re: Master calendar on timestamp

Thu, 17 Mar 2016 11:56:06 GMT

Hi Maurice,

I got the same issue, could you paste the code? I can't Access to that group, it says it's private

cheers

Re: Master calendar on timestamp

mcornips — Fri, 18 Mar 2016 08:49:00 GMT

Hi Ander, do you want to create a master time table or a master calendar table?

Re: Master calendar on timestamp

mcornips — Fri, 18 Mar 2016 09:01:06 GMT

I used the following script with respect to the fact table where the time stamp is recorded:

Timestamp(dt,'hh:mm:ss')                       as Systemtime,
date(floor(dt))                                as Date,
Time(dt, 'hh:mm')                              as Time,
num(hour(dt),'00')&num(minute(dt),'00')        as Timekey,

……..

"dt" = Time stamp in my example

For the master Time table you can use the following script:

///******Create min/max variables*********
MinMax:
LOAD
(Min (Time)) as MinTime,
(Max (Time)) as MaxTime
RESIDENT Facttable;

Let vMinTime = num(peek('Time', 0, 'MinTime'));
Let vMaxTime = num(peek('Time', -1, 'MaxTime'));

////*******Temp Timetable******
TimeField:
Load
time(($(vMinDate) + (recno()/24/60)-1),'hh:mm') AS TempTime
Autogenerate 24 * 60;

DROP TABLE MinMax;

MasterTimetable:
Load
num(hour(TempTime),'00')&num(minute(TempTime),'00') as Timekey,
hour(TempTime)                                       as Hour,
minute(TempTime)                                     as Minute,
'D' & (floor(hour(TempTime)/6) + 1)                 as Dayquarter,

if(hour(TempTime)>=0 and hour(TempTime)<=6,'Night', if(hour(TempTime)>6 and hour(TempTime)<=12,'Morning',
if(hour(TempTime)>12 and hour(TempTime)<=18,'Afternoon', if(hour(TempTime)>18,'Evening')))) as Daytime

RESIDENT TimeField
Order by TempTime ASC;

DROP table TimeField;

Re: Master calendar on timestamp

mcornips — Fri, 18 Mar 2016 09:13:58 GMT

I made a mistake. The script for the dayquarters should be:

if(hour(TempTime)>=0 and hour(TempTime)<=5,'Night', if(hour(TempTime)>5 and hour(TempTime)<=11,'Morning', if(hour(TempTime)>11 and hour(TempTime)=17,'Afternoon', if(hour(TempTime)>17,'Evening')))) as Daytime