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

[#1]

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;

[#2]

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

[whiteline · Accepted solution]

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.