3 Replies Latest reply: Dec 18, 2014 12:29 PM by Marina Carvalho RSS

    Master Calendar with Hour Minute issue

    Marina Carvalho

      Hi all,

      I'm using this piece of code combined with the remaining master calendar script for time with hour minute but I'm having an issue: it seems that I can't get the code to generate the hh:00 time.

       

      Load

      floor(MIN(timestamp#(CAL_DATETIME, 'DD-MM-YYYY hh:mm')))as MinDate

      (...)

      LET vMinDate = peek('MinDate',0);

      (...)

      LOAD

      TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) AS AddedTimeStamp

       

      If I try this for the 1st recno in the 1st iterno, then I'm getting ( for MinDate 41914 -> )

      TimeStamp(41914 + (1/60/24) + (1 -1)) = 02/10/2014 00:01

       

      It seems I can't get the first minute of each hour (hh:00) but only the 2nd (hh:01). I can't figure out what I'm doing wrong here, could you please help?

       

      Thanks you very much,

      Marina

        • Re: Master Calendar with Hour Minute issue
          Marco Wedel

          Hi,

           

          as RecNo() also starts at 1, the last line actually should read:

           

          TimeStamp($(vMinDate) + ((RecNo()-1)/60/24) + (IterNo() -1)) AS AddedTimeStamp


          but if you post some more of this script, then maybe we can abbreviate it a bit.



          hope this helps


          regards


          Marco

            • Re: Master Calendar with Hour Minute issue
              Marco Wedel

              maybe like this:

               

              QlikCommunity_Thread_145648_Pic1.JPG

               

              QlikCommunity_Thread_145648_Pic2.JPG

               

              table1:
              LOAD Timestamp#(CAL_DATETIME, 'DD-MM-YYYY hh:mm') as CAL_DATETIME
              Inline [
              CAL_DATETIME
              10-02-2014 12:34
              12-02-2014 18:24
              14-02-2014 03:04
              ];
              
              tabCalendar:
              LOAD *,
                  DayName(AddedTimeStamp) as Date,
                  Hour(AddedTimeStamp) as Hour,
                  Day(AddedTimeStamp) as Day,
                  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;
              LOAD Timestamp(MinDate+(IterNo()-1)/1440, 'DD-MM-YYYY hh:mm') as AddedTimeStamp
              While MinDate+(IterNo()-1)/1440<=MaxDate;
              LOAD Floor(Min(CAL_DATETIME)) as MinDate,
                  Ceil(Max(CAL_DATETIME)) as MaxDate
              Resident table1;
              

               

              hope this helps

               

              regards

               

              Marco

              • Re: Master Calendar with Hour Minute issue
                Marina Carvalho

                YES, Thank you very much! That was exactly the problem, thanks!

                Problem solved

                 

                Also, thank you for the reply with the master calendar!!

                 

                 

                Cheers,

                Marina