2 Replies Latest reply: Nov 11, 2014 12:54 AM by anbu cheliyan RSS

    Call centre mapping

    john gu

      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.

        • Re: Call centre mapping
          Sergey Makushinsky

          Hi,

          intervalmatch()

           

          Regards,

          Sergey

          • Re: Call centre mapping
            anbu cheliyan

            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;