topic Re: Difference between two times in QlikView

Difference between two times

Karl_Hart — Tue, 16 Dec 2014 16:37:42 GMT

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

Re: Difference between two times

Michiel_QV_Fan — Tue, 16 Dec 2014 16:43:07 GMT

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.

Re: Difference between two times

richard_chilvers — Tue, 16 Dec 2014 16:44:21 GMT

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?

Re: Difference between two times

Karl_Hart — Tue, 16 Dec 2014 16:52:44 GMT

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

Re: Difference between two times

Karl_Hart — Tue, 16 Dec 2014 16:53:45 GMT

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.

Re: Difference between two times

richard_chilvers — Tue, 16 Dec 2014 16:57:24 GMT

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.

Re: Difference between two times

MK_QSL — Tue, 16 Dec 2014 17:18:39 GMT

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;

Re: Difference between two times

MK_QSL — Tue, 16 Dec 2014 17:21:32 GMT

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

MarcoWedel — Tue, 16 Dec 2014 17:26:10 GMT

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

Re: Difference between two times

rwunderlich — Tue, 16 Dec 2014 17:50:29 GMT

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

Re: Difference between two times

its_anandrjs — Tue, 16 Dec 2014 18:01:32 GMT

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

Re: Difference between two times

MarcoWedel — Tue, 16 Dec 2014 19:59:19 GMT

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