Call centre mapping

[john9inno]

Hi all,

I am new to QV.

I have tried this for few days.

However, I couldn't complete this from the top to bottom.

basically, I would like to map phone call records with sales order based on call operator and time stamp.

(call Operator can't accept 2 different calls at the same time.)

here is the sample.

call record

Call  Start time	Call End time	Operator
29/10/2014 15:51	2014-10-29 16:10:00	John Smith
29/10/2014 15:51	29/10/2014 16:02	Jim Parker
29/10/2014 16:14	2014-10-29 16:18:00	John Smith

sales order

Order ID	Created time	Operator
SD123	29/10/2014 16:01	John Smith
SD124	29/10/2014 16:01	Jim Parker

In this senario, first call needs to be mapped with Order-SD123

second call - SD124 and last call should have no order against it.

Can any one pleas give me an idea how i can resolve this?

any comment will be valuable.

thanks.

[SergeyMak]

Hi,

intervalmatch()

Regards,

Sergey

[anbu1984 · Accepted solution]

Use Alt and multiple timestamp formats according to your input

SalesOrder:

Load OrderID,Timestamp#(Createdtime,'YYYY-MM-DD hh:mm') As Createdtime,Operator Inline [

OrderID,Createdtime,Operator

SD123,2014-10-29 16:01,John Smith

SD124,2014-10-29 16:01,Jim Parker ];

CallRecord:

Load Timestamp#(CallStarttime,'YYYY-MM-DD hh:mm') As CallStarttime,Timestamp#(CallEndtime,'YYYY-MM-DD hh:mm') As CallEndtime,Operator Inline [

CallStarttime,CallEndtime,Operator

2014-10-29 15:51,2014-10-29 16:10,John Smith

2014-10-29 15:51,2014-10-29 16:02,Jim Parker

2014-10-29 16:14,2014-10-29 16:18,John Smith];

INNER JOIN (SalesOrder)

IntervalMatch (Createdtime,Operator) Load CallStarttime,CallEndtime, Operator Resident CallRecord;

Join (SalesOrder) Load * Resident CallRecord;

Drop Table CallRecord;