16 Replies Latest reply: Jun 6, 2018 1:14 PM by David Forest RSS

    Issue with Time(hh:mm) Master calendar

    Anil Samineni

      Hello Strikers,

       

      I need one favor on master calendar. I may request you to don't share links related stuff. Because, I've tried maximum. With dates there is no problem at all. But when we are doing with Time stamp making problem. Here is the small attachment and off course, Data also added.

       

      What i needed - I want to generate data from 3:20 PM - 11:50 PM because we need to populate each day(Today only date, Future many dates) and that too needed 10 minutes interval for each like what i have in data.


      Use Case - If you look chart there are only <=3:20 PM data but the chart is not looking as expected that's what we are going to show null values as well in future reference.

       

      I just want to generate Time stamp and with zero data points. I have attached report which i need and data set too.

       

      Mock-up attached.

        • Re: Issue with Time(hh:mm) Master calendar
          Manish Kachhia
          MyTable:
          Load *, A as Orig;
          Load
           TimeStamp(TimeStamp#(A,'DD/M/YYYY h:mm:ss TT')) as A, 
              AV, HW, HC, BA
          Inline
          [
           A, AV, HW, HC, BA,
          29/1/2018 12:00:00 AM, 1205.75, 1942.86, 2970.50, 1172.14
          29/1/2018 12:10:00 AM, 1208.75, 1301.08, 1450.05, 1189.35
          29/1/2018 12:20:00 AM, 1211.00, 1301.08, 1450.05, 1189.35
          29/1/2018 12:30:00 AM, 1214.61, 1348.91, 1515.72, 1223.81
          29/1/2018 12:40:00 AM, 1221.50, 2043.19, 3127.88, 1229.67
          29/1/2018 12:50:00 AM, 1224.75, 1706.82, 2339.52, 1232.30
          29/1/2018 1:00:00 AM, 1225.00, 1370.46, 1551.15, 1234.93
          29/1/2018 1:10:00 AM, 1225.00, 1776.97, 1941.07, 1653.89
          29/1/2018 1:20:00 AM, 1225.00, 1776.97, 1941.07, 1653.89
          29/1/2018 1:30:00 AM, 1225.00, 1781.26, 1948.06, 1656.16
          29/1/2018 1:40:00 AM, 1225.00, 1791.66, 1965.10, 1661.57
          29/1/2018 1:50:00 AM, 1225.00, 1791.66, 1965.10, 1661.57
          29/1/2018 2:00:00 AM, 1225.00, 1801.96, 1977.41, 1670.38
          29/1/2018 2:10:00 AM, 1225.00, 1845.37, 2062.43, 1682.57
          29/1/2018 2:20:00 AM, 1225.00, 1845.37, 2062.43, 1682.57
          29/1/2018 2:30:00 AM, 1225.50, 1866.84, 2098.06, 1693.42
          29/1/2018 2:40:00 AM, 1226.00, 1900.22, 2163.34, 1702.89
          29/1/2018 2:50:00 AM, 1226.00, 1900.22, 2163.34, 1702.89
          29/1/2018 3:00:00 AM, 1226.00, 1900.22, 2163.34, 1702.89
          29/1/2018 3:10:00 AM, 1226.00, 1909.60, 2174.52, 1710.92
          29/1/2018 3:20:00 AM, 1226.00, 1909.60, 2174.52, 1710.92
          ];
          
          
          MinMaxDates:  
          LOAD Floor(Min(TimeStamp#(A, 'DD/MM/YYYY h:mm:ss TT'))) as MinDate,   
               Floor(Max(TimeStamp#(A, 'DD/MM/YYYY h:mm:ss TT'))) as MaxDate  
          RESIDENT MyTable;  
           
          LET vMinDate = FieldValue('MinDate', 1);  
          LET vMaxDate = FieldValue('MaxDate', 1);   
           
          DROP TABLE MinMaxDates;   
          
          
          
          
          CalendarTemp:  
          Load 
           *,
           Time(Frac(A)) as A_Time;
          LOAD 
              TimeStamp($(vMinDate) + (RecNo()/6/24) + (IterNo() -1)) as A
          AUTOGENERATE 143
          WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  
          
          
           
          

          Capture.JPG

          • Re: Issue with Time(hh:mm) Master calendar
            Marcus Sommer

            I doubt that I would ever integrate a time-part into a master-calendar else I would (beside the master-calendar) use a master time-table by splitting the timestamp into a date and a time. This results in clean tables without any conflicts between them or by calculations and improved the performance: The Importance Of Being Distinct

             

            - Marcus

              • Re: Issue with Time(hh:mm) Master calendar
                Anil Samineni

                I already placed about performance because of each seconds will produce as we have seconds in Time stamp. But, They want to be forcibly? The reason behind if we have Ctrl+M schedule with all time-stamp would better if they make from ETL transaction with zero data points. But they are not doing that as already flowing thru to server.

                 

                To make it more clear, Whatever QVW file is there I've done the same with 60/60/24 as we have time sections and generating same using 60*60*24. The problem here is it is not considering the data as HH:MM format with the data.

              • Re: Issue with Time(hh:mm) Master calendar
                Marco Wedel

                Hi,

                 

                I guess the issue indeed lies with your excel source.

                Adding decimal places to your timestamp A reveals that it's off by a fraction of a second.

                 

                QlikCommunity_Thread_303550_Pic2.JPG

                 

                 

                One solution to correct this behaviour might be to round to the next multible of 10 minutes:



                table1:
                LOAD Timestamp(A,'DD/MM/YYYY hh:mm:ss TT') as A,
                     Timestamp#(Timestamp(Round(A,'00:10:00'),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as DateTime,
                     AV,
                     HW,
                     HC,
                     BA
                FROM [lib://SampleDataSet] (ooxml, embedded labels, table is Sheet1);
                
                tabCalendar:   
                LOAD Timestamp#(Timestamp(MinDate+(IterNo()-1)*'00:10:00','DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as DateTime
                While MinDate+(IterNo()-1)*'00:10:00' < MaxDate;  
                LOAD DayStart(Min(A)) as MinDate,  
                     DayEnd(Max(A)) as MaxDate  
                Resident table1;
                



                QlikCommunity_Thread_303550_Pic1.JPG

                 

                hope this helps

                 

                regards

                 

                Marco

                  • Re: Issue with Time(hh:mm) Master calendar
                    Anil Samineni

                    This deserve and get relief for me. But, My real question is - If you look attachment there are null values first thing is i want to remove nulls (I used this object Extension - https://github.com/NielsLindberg/Qliksense.Extension.amWaterfall)

                     

                    From there, If i have more dates nulls getting produced for that, Same chart i want to compare Today's and Last Date in same chart. In between they want vertical line. Can you check the original file if you have time.

                     

                    Requirement - If 2 days data come thru same chart we need like. Let's assume if i have data from 10:00 AM - 3:00 PM for Today's data and same from yesterday data like 5:00 PM - 11:00 PM. I want to show only them. That means X-Axis need to be 10 - 3 and it should start from 4'o clock it self (In our case, Data should start from 5 PM not from 12:00 AM again). Continuation need to be show.


                    Does it make sense?