Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

achakilam1022
Contributor

finding previous value

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!

Tags (1)
1 Solution

Accepted Solutions

Re: finding previous value

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

6 Replies

Re: finding previous value

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
Contributor

Re: finding previous value

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!

Re: finding previous value

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

achakilam1022
Contributor

Re: finding previous value

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!

Re: finding previous value

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
Contributor

Re: finding previous value

Thanks, Ruben

Community Browser