topic Aggregating Call Detail Records Per Call ID in QlikView

Aggregating Call Detail Records Per Call ID

Thu, 02 Feb 2012 07:46:26 GMT

Hi,

I do not know whether anybody has delt with such a problem in the past, but it is a classical problem in telecoms world. Maching call detail records per call id.

In more detail:

I have a bunch of call detail records that record that start of the call, the end of the call, and all the events during the call. As you can imagine, these are separate records. Many records per call. Each call is identified by the call_id. Here is the structure of each record:

call_event_datetime, call_id, queue, agent, verb

I have these events stored in a QVD file (after having fetched them from a MySQL database). And they are quite a lot. 20K records per day.

The point here is that I want to answer questions like the following:

"AVERAGE CALL LENGTH on a Daily Basis"

What is the best way to do that?

One could say. Go and build a table like the following

call_id, start_datetime, end_datetime, call_length

The calculate the AVERAGE on call_length after selecting the calls on a daily basis.

Is that the solution? And how do I create such a table?

Shall I approach this problem completely differently?

Aggregating Call Detail Records Per Call ID

Thu, 15 Mar 2012 19:05:41 GMT

Panagiotis, why don't you aggregate date based on the keys you define on a new QVD file? Then you can use Average formaulas on a graph layout object where you display the data. Diamantis.

Re: Aggregating Call Detail Records Per Call ID

rwunderlich — Fri, 16 Mar 2012 04:49:00 GMT

I assume that field "verb" identifes the event like "CallStart", "CallEnd", etc. There are at least a couple of approaches. You are correct in assuming that's it's best to build up a singe row for each call.

A simple approach is use a series of JOINs selecting each relevant event type in turn, and then doing a subtraction of the new fields to derive the duration.

Calls:

LOAD

call_id, queue, agent,

call_event_datetime as Start_datetime

RESIDENT CDR.qvd (qvd)

WHERE verb='CallStart' // or whatever the indicator is

;

LEFT JOIN (Calls)

LOAD

call_id,

call_event_datetime as End_datetime

RESIDENT CDR.qvd (qvd)

WHERE verb='CallEnd' // or whatever the indicator is

;

LEFT JOIN (Calls)

LOAD

call_id,

interval(End_datetime - Start_datetime, 'hh:mm:ss') as call_length

RESIDENT Calls

;

// Calls table now contains call_id, queue, agent, Start_datetime, EndDatetime, call_length

-Rob

http://robwunderlich.com