20 Replies Latest reply: Aug 19, 2016 2:57 AM by Barbara Greenhill RSS

    Master Calendar- how to include hour values?

    Barbara Greenhill

      Hi,

       

      I am new to qklik view and do not manage the date and time stuff very well until now.

       

      I am looking at inflow data, which I load from excel. Since there are missing values I would like to show them grafically, but my original data does not contain the missing values. That's why I am vreating a master calendar, which works fine, but only until months.

       

      When I try to inlcude hours it does not work. I have tried several stuff now from the forum, but I can't make it work. Anybody that can help me?  See below, how I load the data from excel and the skript for the calendar:

       

      For i=0 to 12

      let vSheet = 2003+$(i);

       

      RAW:

      LOAD

      timestamp#(Dato,'DD/MM/YYYY hh:mm:ss') as Dato2,

      date(floor(Dato),'DD-MMM-YYYY') as Dato_RL,

       

      Year(Dato) as Year_RL,

      month(Dato) as Month_RL,

      Day(Dato) as Day_RL,

      time(Dato,'hh:mm') as Time_RL,

           [Q tilløb] as Qtilløb_RL,

           [Q bypass]as Qbypass_RL,

           '$(vSheet)' as File_Year_RL

      FROM

      [..\Tilløb\RL_timeflow.xlsx]

      (ooxml, embedded labels, table is [$(vSheet)], filters(

      Remove(Row, Pos(Top, 1)),

      Remove(Row, Pos(Top, 1)),

      Remove(Row, Pos(Top, 1)),

      Remove(Row, Pos(Top, 1)),

      Remove(Row, Pos(Top, 2)),

      Remove(Row, Pos(Top, 2)),

      Remove(Row, RowCnd(CellValue, 3, StrCnd(start, '-')))

      ));

       

       

      next;

       

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12); 

       

      Temp: 

      Load 

                     min(Dato_RL) as minDate, 

                     max(Dato_RL) as maxDate 

      Resident RAW; 

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

      DROP Table Temp; 

       

       

      CalendarTemp: 

      LOAD 

                     $(varMinDate) + Iterno()-1 As Num, 

                     Date($(varMinDate) + IterNo() - 1) as TempDate 

                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

       

      MasterCalendar: 

      Load 

                     //TempDate AS Dato_RL,

                     TempDate AS Dato_RL, 

                     week(TempDate) As Week, 

                     Year(TempDate) As Year, 

                     Month(TempDate) As Month, 

                     Day(TempDate) As Day, 

                     Hour(time(TempDate)) as Hour,

                     YeartoDate(TempDate)*-1 as CurYTDFlag, 

                     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                     WeekDay(TempDate) as WeekDay 

      Resident CalendarTemp 

      Order By TempDate ASC; 

      Drop Table CalendarTemp;

       

      Thanks in advance!

        • Re: Master Calendar- how to include hour values?
          Sunny Talwar

          Have you checked this thread?

          Master Calendar with Hour

           

          Alternatively, I suggest looking in here as well:

          The Master Time Table

          • Re: Master Calendar- how to include hour values?
            Gysbert Wassenaar

            Change the CalendarTemp to include hours:

             

            CalendarTemp:

            LOAD

                           $(varMinDate) + Iterno()-1 As Num,

                           Date($(varMinDate) + IterNo() - 1 +( RecNo()-1)/24) as TempDate

                           AutoGenerate 24 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

              • Re: Master Calendar- how to include hour values?
                Jonathan Dienst

                Shouldn't that line read:

                 

                Date($(varMinDate) + IterNo() - 1 + (RecNo()-1)/24) as TempDate,

                  • Re: Master Calendar- how to include hour values?
                    Gysbert Wassenaar

                    Absolutely. Thanks for pointing out that error.

                      • Re: Master Calendar- how to include hour values?
                        Barbara Greenhill

                        Hi guys, thanks a lot! My hours are generated BUT is not connected to the data... The year, month and day are and the data changes when I change from 2003-2004 for example...

                         

                        Is there something in the lower part of the code I have to change?

                        MasterCalendar: 

                        Load 

                                       //TempDate AS Dato_RL,

                                       TempDate AS Dato_RL, 

                                       week(TempDate) As Week, 

                                       Year(TempDate) As Year, 

                                       Month(TempDate) As Month, 

                                       Day(TempDate) As Day, 

                                       Hour(TempDate)AS Hour,

                                       YeartoDate(TempDate)*-1 as CurYTDFlag, 

                                       YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                                       inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                                       date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                                       ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                                       Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                                       WeekDay(TempDate) as WeekDay 

                        Resident CalendarTemp 

                        Order By TempDate ASC; 

                        Drop Table CalendarTemp;

                         

                         

                        I actually also found out that my 'hour' data in the source data is not uniform...I have data that is 16:00:00, but as well entries like 15:59:59. Might that be the problem? I am just in the process to find out how I convert all XX:59:59 data into the whole hour...

                         

                        Many thanks in advance!

                          • Re: Master Calendar- how to include hour values?
                            Gysbert Wassenaar
                            I have data that is 16:00:00, but as well entries like 15:59:59. Might that be the problem?

                            Yes, that's the problem. You need to round down your source hour data to whole hours too.

                              • Re: Master Calendar- how to include hour values?
                                Barbara Greenhill

                                I actually need to round up, because 15:59:59 should be 16:00....

                                  • Re: Master Calendar- how to include hour values?
                                    Barbara Greenhill

                                    Got it rounded, but get 00:00 twice.... But I fell over something new of course ;-).

                                     

                                    When I round the data and then check it whether it has the same date format as the original data, I can see that if I use 'timestamp' in the list box properties, it returns me a wrong date such as 30/12/1899 and not 2003 etc., while my loaded data is just from 2003-2015.

                                     

                                    I have then also checked my master calendar and when I select timestamp in my master calendar then I also get a fx 1905, while when I use the default setting I get nicely 2003, 2004, 2005 etc.. And the data is linked to the Dato_RL data which comes in the right format...

                                     

                                    When I then check which timestamp my related tabel (Dato_RL) has, then it goes from 2003-2015... so it has the right data and timestamp. It shows 24 values for each day from 2003-2015 with its corresponding time 00:00:00, 01:00:00, 02:00:00 etc. 

                                     

                                    I am a little bit confused I have to admit...

                                     

                                    I used this skript to load the dates from the origianl table:

                                     

                                    date(floor(Dato),'DD-MMM-YYYY') as Dato_RL,

                                    Time(Floor(Frac(Dato),1/24),'hh') as hours_data,

                                    Time(Round(Frac(Dato),1/24/60),'hh:mm') as Time_data,

                                     

                                    I have to make sure that all give the same timestamp right?

                                      • Re: Master Calendar- how to include hour values?
                                        Gysbert Wassenaar
                                        it returns me a wrong date such as 30/12/1899

                                        Then you need to check that variable varMinDate gets a correct value. It looks like a string like 11/22/2012 was evaluated as an expression: 11 divided by 22 divided by 2012.

                                          • Re: Master Calendar- how to include hour values?
                                            Barbara Greenhill

                                            Good morning :-). The funny thing is that it reads the varMinDate and the varMaxDate correctly, I get the correct integer for it AND the master calendar selects the right data when it comes to year, month and day. So maybe it doesn't matter that the master calendar does not show the right timestamp... Since it selects the right data if I leave the 'default setting' in the list box...

                                            However things don't work anymore with hours...

                                             

                                             

                                            I have attached my data now. Below the skript how I load it and again the skript for my master calendar without hours still.

                                             

                                             

                                            DATA LOADING

                                             

                                             

                                            For i=0 to 12

                                            let vSheet = 2003+$(i);

                                             

                                             

                                             

                                             

                                            Renseanlæg_Lynetten:

                                            RAW:

                                            LOAD

                                            //date(timestamp#(Dato,'DD/MM/YYYY hh:mm:ss'),'DD-MMM-YYYY') as Dato,

                                            //timestamp#(Dato,'DD/MM/YYYY hh:mm:ss') as Dato_RL,

                                            timestamp#(Dato,'DD/MM/YYYY hh:mm:ss') as Dato_RL,

                                            date(floor(Dato),'DD-MMM-YYYY') as Dato_RL_RL,

                                             

                                             

                                             

                                             

                                            Year(Dato) as Year_RL,

                                            month(Dato) as Month_RL,

                                            Day(Dato) as Day_RL,

                                            time(Dato,'hh:mm') as Time_RL,

                                                [Q tilløb] as Qtilløb_RL,

                                            //    QNORD as QNORD_RL,

                                            //    QSYD1 as QSYD1_RL,

                                            //    QSYD2 as QSYD2_RL,

                                                [Q bypass]as Qbypass_RL,

                                                '$(vSheet)' as File_Year_RL

                                            FROM

                                            [..\Tilløb\RL_timeflow.xlsx]

                                            (ooxml, embedded labels, table is [$(vSheet)], filters(

                                            Remove(Row, Pos(Top, 1)),

                                            Remove(Row, Pos(Top, 1)),

                                            Remove(Row, Pos(Top, 1)),

                                            Remove(Row, Pos(Top, 1)),

                                            Remove(Row, Pos(Top, 2)),

                                            Remove(Row, Pos(Top, 2)),

                                            Remove(Row, RowCnd(CellValue, 3, StrCnd(start, '-')))

                                            ));

                                             

                                             

                                             

                                             

                                            next;

                                             

                                             

                                            MASTER CALENDAR

                                             

                                             

                                            QuartersMap: 

                                            MAPPING LOAD  

                                            rowno() as Month, 

                                            'Q' & Ceil (rowno()/3) as Quarter 

                                            AUTOGENERATE (12); 

                                             

                                            Temp: 

                                            Load 

                                                           min(Dato_RL_RL) as minDate, 

                                                           max(Dato_RL_RL) as maxDate 

                                            Resident RAW; 

                                             

                                            Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                                            Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                                            DROP Table Temp; 

                                             

                                             

                                             

                                             

                                            CalendarTemp: 

                                            LOAD 

                                                           $(varMinDate) + Iterno()-1 As Num, 

                                                           Date($(varMinDate) + IterNo() - 1) as TempDate 

                                                           AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                                                          

                                            MasterCalendar: 

                                            Load 

                                                           //TempDate AS Dato_RL,

                                                           TempDate AS Dato_RL_RL, 

                                                           week(TempDate) As Week, 

                                                           Year(TempDate) As Year, 

                                                           Month(TempDate) As Month, 

                                                           Day(TempDate) As Day, 

                                                           Hour(time(TempDate)) as Hour,

                                                           YeartoDate(TempDate)*-1 as CurYTDFlag, 

                                                           YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                                                           inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                                                           date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                                                           ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                                                           Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                                                           WeekDay(TempDate) as WeekDay 

                                            Resident CalendarTemp 

                                            Order By TempDate ASC; 

                                            Drop Table CalendarTemp;

                                            • Re: Master Calendar- how to include hour values?
                                              Barbara Greenhill

                                              Well, now the big question- how can I upload my data into the chat...