Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
I appreciate any help with this.
Thanks!
Right, Peek has to be on ID field, not in ID_orig:
if(len(trim(ID_Orig))=0, if(Phone=peek(Phone),If(CALL_ID>peek(CALL_ID),peek(ID))),ID_Orig) as ID
Hi Amuktha, I think it should work, the only thing is that if you load an 'ID' field in the last table it will return an error as there is already an 'ID' field.
Try changing the '*' to field names, expect ID, that will be loaded as it is: checking with Peek())
Or rename the field:
Rename field ID to ID_Orig;
NoConcatenate
ManualCalls2:
Load *,
if(len(trim(ID_Orig))=0, if(Phone=peek(Phone),If(CALL_ID>peek(CALL_ID),peek(ID_Orig))),ID_Orig) as ID
Resident ManualCalls;
Hi Ruben,
I tried
Rename field ID to ID_Orig;
NoConcatenate
ManualCalls2:
Load *,
if(len(trim(ID_Orig))=0, if(Phone=peek(Phone),If(CALL_ID>peek(CALL_ID),peek(ID_Orig))),ID_Orig) as ID
Resident ManualCalls;
But, it doesnt work. Any thoughts?
Also, the original code works only if there is a single Inbound/Outbound record for a matching Manual call phone number. It fails when it has to look up at multiple Inbound/Outbound records to fill the missing ID on Manual record
Thanks!
What exactly doen't works? seems to work with your sample data
Hey Ruben,
I appreciate your response.
I couldnt open the qvw as I use Qlik Sense Server.
Could you take a look at the attached Input2 file? I'm unable to generate ID for the last row in the data set(ID has to be 4). Sorry for not mentioning the full characteristics of the original data set earlier.
Thanks!
Right, Peek has to be on ID field, not in ID_orig:
if(len(trim(ID_Orig))=0, if(Phone=peek(Phone),If(CALL_ID>peek(CALL_ID),peek(ID))),ID_Orig) as ID
Thanks, Ruben