Master calendar on timestamp

[mcornips]

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;

[sunny_talwar]

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

[mcornips]

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.

[#4]

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

[mcornips]

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

[mcornips]

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;

[mcornips]

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