14 Replies Latest reply: Feb 16, 2016 8:22 AM by omar bensalem RSS

    Calendar with time (timestamp)

    Johan Adolfsson

      Hi!

      I have a MasterCalendar that works great. I am now trying to customize it so that it holds time too, using timestamp.
      I saw an example here on how to do it but I can't get my code to work. Can anybody see what I am doing wrong?
      Any help is appreciated
      I include my qvw file. I keep getting error message : Error in expression: ')' expected
      For what I can tell I am not missing any ")"...
      Below is the code that qlikview doesn't like...[:)]

      TempCalendar:

      LOAD

      $(varMinTimestamp) + rowno() - 1 AS Num,

      Timestamp(Timestamp#($(varMinTimestamp), 'YYYYMMDD hh:mm:ss') + rowno() - 1) AS tempTimestamp

      AUTOGENERATE

      $(varMaxTimestamp) - $(varMinTimestamp) + 1;

       

      Thankful for any help

       

        • Calendar with time (timestamp)
          bandariarun

          hi dude,

          go through this link, it may helps u..

          http://community.qlik.com/forums/t/35921.aspx

          regards

          A'run'

          • Re: Calendar with time (timestamp)
            Miguel Angel Baeyens de Arce

            Hello Johan,

            If what you are looking for is a calendar that returns all possible hour:minute combination for each day, you should try with something like

             

            MyTable:
            LOAD DayStart(TimeStamp(TimeStamp#('01/01/2011 00:00', 'DD/MM/YYYY hh:mm') + (RecNo()/60/24) + (IterNo() -1))) AS AddedDate, 
                 TimeStamp(TimeStamp#('01/01/2011 00:00', 'DD/MM/YYYY hh:mm') + (RecNo()/60/24) + (IterNo() -1)) AS AddedTimeStamp
            AUTOGENERATE 1439 WHILE Num(Date('01/01/2011') + IterNo() -1) <= Num(Date('31/01/2011'));
            

             

            AddedDate is the date, while AddedTimeStamp is the complete timestamp with hours and minutes.

            Is that what you are looking for?

              • Calendar with time (timestamp)
                Johan Adolfsson

                Thank you guys for your replys.

                Miguel, you're close. Maybe I just didn't understand how to implement your code in my qvw .... What I am after is, if you check out the qvw file, is to create a calendar that holds all possible hour and minute (also likely seconds) combination for each day between oldest timestamp in MyTimestamp and the newest timestamp in MyTimestamp.

                 

                This is how I find oldest and newest timestamp.
                LOAD
                min(MyTimestamp) as MinTimestamp,
                max(MyTimestamp) as MaxTimestamp
                Resident MyTable;

                 

                This is how I create my calendar. (This is also where I get an error, see earlier post)
                LOAD

                $(varMinTimestamp) + (rowno() - 1) AS Num,

                 

                Timestamp(Timestamp#($(varMinTimestamp), 'YYYYMMDD hh:mm:ss') + (rowno() - 1)) AS tempTimestamp

                AUTOGENERATE

                $(varMaxTimestamp) - $(varMinTimestamp) + 1;

                 

                 

                 

                 

                Thanks, Johan

                 



                 

                 

                 

                 

                  • Re: Calendar with time (timestamp)
                    Miguel Angel Baeyens de Arce

                    Hello Johan,

                    TimeStamp() returns a decimal number (in QlikView, hours are the 1/24 part of a day, and minutes 1/24/60, and seconds and so on). So instead of a timestamp, use a date, which is what you set in your min and max values for dates, so the code looks like

                     

                    MinMaxDates:
                    LOAD Floor(Min(TimeStamp#(TimeStamp, 'DDMMMYYY:hh:mm:ss'))) AS MinDate, 
                         Floor(Max(TimeStamp#(TimeStamp, 'DDMMMYYY:hh:mm:ss'))) AS MaxDate
                    RESIDENT MyTable;
                    
                    LET vMinDate = FieldValue('MinDate', 1);
                    LET vMaxDate = FieldValue('MaxDate', 1); 
                    
                    DROP TABLE MinMaxDates; 
                    
                    CalendarTemp:
                    LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date 
                         TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS AddedTimeStamp
                    AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate)); 
                    
                    Calendar:LOAD AddedTimeStamp AS MyTimeStamp, 
                         Year(AddedTimeStamp) AS MyYear,// And so
                    

                     

                    This will create a Calendar with all possible timestamps between the first second (00:00:00) of the first date to the last second (23:59:59) of the last date in your table. It will take long to load depending on the number of dates you have in your model (86399 records per day).

                    Hope that helps

                  • Re: Calendar with time (timestamp)
                    Marina Carvalho

                    Hi Miguel,

                     

                    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 vMinDate 41914:

                    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: Calendar with time (timestamp)
                      omar bensalem

                      Hi Miguel,

                       

                      I was trying to do the same thing so I've happily used your script.

                       

                      here how I did:

                       

                      MinMaxDates: 

                      LOAD Floor(Min(TimeStamp#(TimeStamp, 'DDMMMYYY:hh:mm:ss'))) AS MinDate,  

                           Floor(Max(TimeStamp#(TimeStamp, 'DDMMMYYY:hh:mm:ss'))) AS MaxDate 

                      RESIDENT MyTable; 

                       

                      LET vMinDate = FieldValue('MinDate', 1); 

                      LET vMaxDate = FieldValue('MaxDate', 1);  

                       

                      DROP TABLE MinMaxDates;  

                       

                      CalendarTemp: 

                      LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date  

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

                      AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  

                       

                      Calendar:LOAD AddedTimeStamp AS MyTimeStamp,  

                           Year(AddedTimeStamp) AS MyYear

                       

                       

                      MasterCalendar:

                      LOAD    AddedTimeStamp  AS MyTimestamp,

                      Year(AddedTimeStamp)  AS MyYear,

                      Month(AddedTimeStamp)  AS MyMonth,

                      Day(AddedTimeStamp)  AS MyDay,

                      Hour(AddedTimeStamp)  AS MyHour,

                      Minute(AddedTimeStamp)  AS MyMinute,

                      Second(AddedTimeStamp)  AS MySecond

                       

                      RESIDENT CalendarTemp

                      ORDER BY AddedTimeStamp  ASC;

                       

                      DROP TABLE CalendarTemp;

                       

                       

                      All went good and I can see my new fields (MyHour etc) generated in the correct time frame.

                       

                      Now, the problem is that when I try to filter with MyHour, MyMinute and so one, the graphs I've created doesn't change.

                      In fact, filtering with those fields does not affect the the Date_Timestamp field box (does not turn green nore even white) when I select an hour in the MyHour field.

                       

                      Why that?

                      What should I do?

                      I'm reaaly confused !