2 Replies Latest reply: Oct 27, 2012 4:18 AM by whiteline _ RSS

    How to find the maximum number of overlaps in a list of time ranges

    John Lobaugh

      Any ideas on how to implement this in Qlikview ?

       

      Given a list of time ranges I need to find the maximum number of overlaps.

       

      For example, I have a set of telephone calls made in a call center. The call data specifies when calls start and when they end.

       

      CallStart   CallEnd

      2:22:22 PM  2:22:33 PM

      2:22:35 PM  2:22:42 PM

      2:22:36 PM  2:22:43 PM

      2:22:46 PM  2:22:54 PM

      2:22:49 PM  2:27:21 PM

      2:22:57 PM  2:23:03 PM

      2:23:29 PM  2:23:40 PM

      2:24:08 PM  2:24:14 PM

      2:27:37 PM  2:39:14 PM

      2:27:47 PM  2:27:55 PM

      2:29:04 PM  2:29:26 PM

      2:29:31 PM  2:29:43 PM

      2:29:45 PM  2:30:10 PM

       

       

       

      I need to find the maximum number of telephone calls that occurred at the same time (ie the maximum number of lines that were in use).

       

      The easiest solution is to just lump all the call times and types into a single list and sort them by call time. Then go through the list keeping a running count. Increment the count when a call starts and decrement it when a call ends. The maximum value the count hits is your answer.

       

      Here’s what I have come up with so far for my load script. I can get a table that has all the call times and the type for each (S for call start and E for call end) but I’m a little uncertain how to proceed from there.

       

       

      CallData:

      LOAD * INLINE [

          CallStart, CallEnd

          2:22:22 PM  , 2:22:33 PM

          2:22:35 PM  , 2:22:42 PM

          2:22:36 PM  , 2:22:43 PM

          2:22:46 PM  , 2:22:54 PM

          2:22:49 PM  , 2:27:21 PM

          2:22:57 PM  , 2:23:03 PM

          2:23:29 PM  , 2:23:40 PM

          2:24:08 PM  , 2:24:14 PM

          2:27:37 PM  , 2:39:14 PM

          2:27:47 PM  , 2:27:55 PM

          2:29:04 PM  , 2:29:26 PM

          2:29:31 PM  , 2:29:43 PM

          2:29:45 PM, 2:30:10 PM

      ];

       

      Calls_:

      LOAD * INLINE [

          CallType, CallTime_

      ];

       

      CONCATENATE(Calls_)

      Load

           'S' As  CallType,

           CallStart as CallTime_

      Resident CallData;

       

      //  

      OUTER JOIN (Calls_)

      Load

           'E' As  CallType,

           CallEnd as CallTime_

      Resident CallData;

       

       

       

       

       

        • Re: How to find the maximum number of overlaps in a list of time ranges
          Lav Jain

          can u post, how do u want to see the final result for the sample u posted in ur ques ?

          • Re: How to find the maximum number of overlaps in a list of time ranges
            whiteline _

            Hi.

             

            It would be easier if you would first have done it with Excel for example.

             

            You could do some calculations in script:

            // Load your data with a correct time format
            // Add IDs for calls
            CallData:
            LOAD
             ID,
             Time#(CallStart, 'h:mm:ss tt') as CallStart,
             Time#(CallEnd, 'h:mm:ss tt') as CallEnd
            INLINE [
            ID,    CallStart, CallEnd
            1,    2:22:22 PM  , 2:22:33 PM
            2,    2:22:35 PM  , 2:22:42 PM
            3,    2:22:36 PM  , 2:22:43 PM
            4,    2:22:46 PM  , 2:22:54 PM
            5,    2:22:49 PM  , 2:27:21 PM
            6,   2:22:57 PM  , 2:23:03 PM
            7,    2:23:29 PM  , 2:23:40 PM
            8,    2:24:08 PM  , 2:24:14 PM
            9,    2:27:37 PM  , 2:39:14 PM
            10,    2:27:47 PM  , 2:27:55 PM
            11,    2:29:04 PM  , 2:29:26 PM
            12,    2:29:31 PM  , 2:29:43 PM
            13,    2:29:45 PM  , 2:30:10 PM
            ];
            
            //First create a full list of events with ID markers.
            [TimeSpans]:
            LOAD
             CallStart as TimeSpan, 
             ID as IDByStart,
             ID
            Resident CallData;
            
            Concatenate(TimeSpans)
            LOAD
             CallEnd as TimeSpan,
             ID as IDByEnd,
             ID
            Resident CallData;
            
            // Order the list of events and calculate accumulated number of active start/end events
            TimeSpansOrdered:
            LOAD
             ID,
             IDByStart,
             IDByEnd,
             TimeSpan, 
             if(not IsNull(IDByStart), rangesum(peek('AccumStartCount'),1), rangesum(peek('AccumStartCount'))) as AccumStartCount,
              if(not IsNull(IDByEnd), rangesum(peek('AccumEndCount'),1), rangesum(peek('AccumEndCount'))) as AccumEndCount 
            Resident TimeSpans
            Order by TimeSpan /*desc*/;
            
            //Create a field with a correct text represenation of TimeSpans
            [TimeSpansFormatted]:
            LOAD
             TimeSpan,
             if(not isnull(previous(TimeSpan)), dual(TimeSpan & '-' & previous(TimeSpan), TimeSpan), null()) as DualTimeSpan
            Resident TimeSpans
            order by TimeSpan desc;
            
            drop table TimeSpans;
            
            

             

            Then you can just create a chart with  DualTimeSpan as dimension and =(AccumStartCount-AccumEndCount) as expression.

            This will give you the number of overlapping calls for each timespan.