Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Karl_Hart
Contributor

Difference between two times

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

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Difference between two times

You need to get the two events on the same row to be able to calculate the duration. Generic Load would work, but it may be more than you need for just two events. Here's an approach,

Calls:

LOAD [Call ID], Time as StartTime

FROM $(QVD_Path)\CCEvents.qvd (qvd)

WHERE EventID=1;

LEFT JOIN(Calls)

LOAD [Call ID], Time as EndTime

FROM $(QVD_Path)\CCEvents.qvd (qvd)

WHERE EventID=2;

// After the above JOIN,  we now have one row for each Call ID.

// Calculate the Duration of each call.

RIGHT JOIN (Calls)

LOAD *,  interval(EndTime-StartTime,'m:ss') as Duration

RESIDENT Calls;


-Rob


11 Replies
michielvandegoo
Valued Contributor

Re: Difference between two times

Your data isn't on the same row.

Create a crosstable to get 1 row of data for each Call ID and then you have 2 columns for the event id's.

Then you can subtract the columns and get the interval.

richard_chilvers
Valued Contributor

Re: Difference between two times

Hi Karl

Could you clarify your sources of data ?

I would assume you are extracting call start & end times from your calls database, but [Call Start Time] fro example, is not defined at that point?

Karl_Hart
Contributor

Re: Difference between two times

Richard,

The call times are indeed from a database, they come through on two different lines with event_ID 1 and event_ID 2.

I convert them into [Call Start Time] and [Call Answer Time] in the script above

Karl_Hart
Contributor

Re: Difference between two times

Thanks, I'll look into this. I dont know how easy it would be with the large amount of data in the tables in the actual database.

I'll see how I get on.

richard_chilvers
Valued Contributor

Re: Difference between two times

Karl

QV is pretty good at gobbling up large numbers of records (especially with no many fields in each).

But I do something similar to this by creating a QVD file and then using incremental loads (each day). Perhaps you're doing something similar as you refer to a qvd file in your script.

MVP
MVP

Re: Difference between two times

Temp:

LOAD

  [Call ID],

  EventID,

  Time(Time) as 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

];

Final:

Load

  [Call ID],

  EventID,

  Time,

  If([Call ID] = Previous([Call ID]), Time(Previous (Time)-Time)) as Difference

Resident Temp

Order By [Call ID], EventID Desc, Time Desc;

Drop Table Temp;

MVP
MVP

Re: Difference between two times

Temp:

LOAD

  [Call ID],

  EventID,

  Time(Time) as 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

];

Temp2:

Load

  [Call ID],

  Time as RingTime

Resident Temp Where EventID = 1;

Left Join

Load

  [Call ID],

  Time as AnswerTime

Resident Temp Where EventID = 2;

Drop Table Temp;

Final:

Load *, Time(AnswerTime-RingTime) as Difference Resident Temp2;

Drop table Temp2;

Re: Difference between two times

you could use a generic load to get seperate TimeReceived / TimeAnswered fields.

MVP & Luminary
MVP & Luminary

Re: Difference between two times

You need to get the two events on the same row to be able to calculate the duration. Generic Load would work, but it may be more than you need for just two events. Here's an approach,

Calls:

LOAD [Call ID], Time as StartTime

FROM $(QVD_Path)\CCEvents.qvd (qvd)

WHERE EventID=1;

LEFT JOIN(Calls)

LOAD [Call ID], Time as EndTime

FROM $(QVD_Path)\CCEvents.qvd (qvd)

WHERE EventID=2;

// After the above JOIN,  we now have one row for each Call ID.

// Calculate the Duration of each call.

RIGHT JOIN (Calls)

LOAD *,  interval(EndTime-StartTime,'m:ss') as Duration

RESIDENT Calls;


-Rob