Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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


View solution in original post

11 Replies
Michiel_QV_Fan
Specialist
Specialist

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
Specialist
Specialist

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Specialist
Specialist

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.

MK_QSL
MVP
MVP

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;

MK_QSL
MVP
MVP

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;

MarcoWedel

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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