Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

john9inno
Contributor

Call centre mapping

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 timeCall End timeOperator
29/10/2014 15:512014-10-29 16:10:00John Smith
29/10/2014 15:5129/10/2014 16:02Jim Parker
29/10/2014 16:142014-10-29 16:18:00John Smith

sales order

Order IDCreated timeOperator
SD12329/10/2014 16:01John Smith
SD12429/10/2014 16:01Jim 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.

Tags (1)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Call centre mapping

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;

2 Replies
Highlighted
sergey_maka8
Valued Contributor

Re: Call centre mapping

Hi,

intervalmatch()

Regards,

Sergey

anbu1984
Honored Contributor III

Re: Call centre mapping

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;