Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
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;

Community Browser