Difference between two times

[Karl_Hart]

Hi,

I'm looking at data from our phones database and want to know how long it takes to answer our calls. So I have some data that looks like this:

Call ID          EventID          Time

1                    1                    09:00:20

1                    2                    09:00:30

2                    1                    09:10:15

2                    2                    09:10:23

3                    1                    09:15:05

3                    2                    09:15:35

Where EventID = 1 is phone rings and EventID = 2 is call answered.

I've done the following..............

Calls:

LOAD*,

  [Call Answer Time] - [Call Start Time] as [Ring Duration]

  ;

LOAD

    [Call ID],

    if(event_id=1,time(event_time)) as [Call Start Time],

    if(event_id=2,time(event_time)) as [Call Answer Time]

FROM

$(QVD_Path)\CCEvents.qvd

(qvd);

and this isn't returning anything for the [Ring Duration] and I dont know what to do. Any help out there?

How do i get it to only do the Ring Duration calculation only if the Call ID is the same (e.g. I dont want it to do 09:15:35 - 09:00:20)

What am I missing?

Thanks

[its_anandrjs]

Hi,

I thing you miss the Interval function and your load is right try this way also the Rob suggestion is seems right also in this conditions you can use Joins and load two different tables with Event 1 and 2.

CallDetails:

LOAD

    [Call ID],

    if(event_id=1,time(event_time)) as [Call Start Time],

    if(event_id=2,time(event_time)) as [Call Answer Time]

FROM

$(QVD_Path)\CCEvents.qvd

(qvd);

Noconcatenate

Calls:

LOAD *,

InterVal( [Call Answer Time] - [Call Start Time] ,'mm:ss' ) as [Ring Duration]

Resident CallDetails;

Drop Table CallDetails;

Regards

Anand

[MarcoWedel]

Hi Rob,

I agree that a generic load might be a bit overdone in this case, but maybe it helps to simply learn about different approaches to a specific problem.

So one example to generate two seperate time fields for each Call ID might be:

Generic LOAD
  [Call ID],
  Pick(EventID, 'Call Start Time', 'Call Answer Time'),
  Time
INLINE [
    Call ID, EventID, Time
    1, 1, 09:00:20
    1, 2, 09:00:30
    2, 1, 09:10:15
    2, 2, 09:10:23
    3, 1, 09:15:05
    3, 2, 09:15:35
];

The interval calculation is done as a chart expression though.

hope this helps

regards

Marco