Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

If Previous, Peek problem

HI everyone,

I'm having difficulty with the previous and peek function.

The syntax looks like this :

if(CustId= Previous (CustId),Peek ('date1'),null() )as TestPeek

I have shown below the output I am getting, vs the output I require:

   

CustIdDate PurchaseNumCurrent Output Required Output DaysDiff
12345624/06/20151-02/08/201539
12345602/08/2015224/06/201505/08/20153
12345605/08/2015302/08/201512/09/201538
12345612/09/2015405/08/201517/09/20155
12345617/09/2015512/09/2015

Could anyone advise please?

Thanks,

Holly

1 Solution

Accepted Solutions
MVP
MVP

Re: If Previous, Peek problem

You can try loading it as resident and sort them in Descending order and then refer the last record like:

T1: 

LOAD * INLINE [ 

    CustId, Date, PurchaseNum 

    123456, 6/24/2015, 1 

    123456, 8/2/2015, 2 

    123456, 8/5/2015, 3 

    123456, 9/12/2015, 4 

    123456, 9/17/2015, 5 

]; 

NoConcatenate 

T2: 

Load CustId, 

  Date, 

if(CustId= Previous (CustId),Peek ('Date'),null())as TestPeek, 

if(CustId= Previous (CustId),Date#(Peek ('Date'),'MM/DD/YYYY') - Date#(Date, 'MM/DD/YYYY'),null()) as Diff 

  Resident T1 Order By PurchaseNum Desc; 

DROP Table T1; 

3 Replies

Re: If Previous, Peek problem

Hi Holly,

Can you check this is what you need?

T1:

LOAD * INLINE [

    CustId, Date, PurchaseNum

    123456, 6/24/2015, 1

    123456, 8/2/2015, 2

    123456, 8/5/2015, 3

    123456, 9/12/2015, 4

    123456, 9/17/2015, 5

];

NoConcatenate

T2:

Load CustId,

  Date#(Date,'M/D/YYYY') as Date,

  if(CustId=Peek('CustId',RowNo(),'T1'),Date#(Peek('Date',RowNo(),'T1'),'M/D/YYYY'),Null()) as New_Date,

  Date#(Peek('Date',RowNo(),'T1'),'M/D/YYYY')-Date#(Date,'M/D/YYYY') as Diff

  Resident T1;

DROP Table T1;

MVP
MVP

Re: If Previous, Peek problem

You can try loading it as resident and sort them in Descending order and then refer the last record like:

T1: 

LOAD * INLINE [ 

    CustId, Date, PurchaseNum 

    123456, 6/24/2015, 1 

    123456, 8/2/2015, 2 

    123456, 8/5/2015, 3 

    123456, 9/12/2015, 4 

    123456, 9/17/2015, 5 

]; 

NoConcatenate 

T2: 

Load CustId, 

  Date, 

if(CustId= Previous (CustId),Peek ('Date'),null())as TestPeek, 

if(CustId= Previous (CustId),Date#(Peek ('Date'),'MM/DD/YYYY') - Date#(Date, 'MM/DD/YYYY'),null()) as Diff 

  Resident T1 Order By PurchaseNum Desc; 

DROP Table T1; 

Not applicable

Re: If Previous, Peek problem

Thanks, this is exactly what I needed