Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MVP & Luminary
MVP & Luminary

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

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?

Creator
Creator

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

Creator
Creator

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.

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.

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;

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;

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

MVP & Luminary
MVP & Luminary

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