Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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?
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
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.
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.
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;
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.
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