finding previous value

[achakilam1022]

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!

[rubenmarin]

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;

[achakilam1022]

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!

[rubenmarin]

What exactly doen't works? seems to work with your sample data

[achakilam1022]

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!

[rubenmarin · Accepted solution]

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

[achakilam1022]

Thanks, Ruben