6 Replies Latest reply: Mar 18, 2016 5:13 AM by Maurice Cornips RSS

    Master calendar on timestamp

    Maurice Cornips

      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?




        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);




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


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


      DROP TABLE MinMax;




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

          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.




          • Re: Master calendar on timestamp
            Maurice Cornips

            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
                Ander Iglesias Rojo

                Hi Maurice,

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



                  • Re: Master calendar on timestamp
                    Maurice Cornips

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

                      • Re: Master calendar on timestamp
                        Maurice Cornips

                        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*********
                        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******
                        time(($(vMinDate) + (recno()/24/60)-1),'hh:mm') AS TempTime
                        Autogenerate 24 * 60;

                        DROP TABLE MinMax;

                        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
                            Maurice Cornips

                            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