6 Replies Latest reply: Dec 22, 2017 2:14 PM by Amuktha Chakilam RSS

    finding previous value

    Amuktha Chakilam

      Hi all,

       

      I'm working with call log data ;There are three types of calls: Inbound,Outbound, Manual


      All Inbound and Outbound records have ID and all Manual records have no ID

      I'm trying to find the ID of the Manual record by finding the most recent call on that Phone number which has a ID.


      I've tried following code; It works in a few cases but not all. Please help me figure out what should be corrected in this code

       

      Reference:

      Load *

           from .....input.xlsx;

      //PFA of input data. (The original data contains multiple Phone Numbers)

       

       

      NoConcatenate

      ManualCalls:

      Load *

           Resident Reference

      Order By

           Phone,CALL_ID;


      Drop Table Reference;



      NoConcatenate

      ManualCalls2:

      Load *,

           if(len(trim(ID))=0, if(Phone=peek(Phone),If(CALL_ID>peek(CALL_ID),peek(ID))),ID) as ID

      Resident ManualCalls;


      Drop Table ManualCalls;


      Expected Output for the data in the attachment: (The original data contains multiple Phone Numbers)

      Output.JPG

       

       


      I appreciate any help with this.


      Thanks!