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?

       

      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

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

                 

                cheers

                  • 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*********
                        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
                            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