topic Re: If Previous, Peek problem in QlikView

If Previous, Peek problem

Thu, 08 Oct 2015 14:38:45 GMT

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:

CustId	Date	PurchaseNum	Current Output	Required Output	DaysDiff
123456	24/06/2015	1	-	02/08/2015	39
123456	02/08/2015	2	24/06/2015	05/08/2015	3
123456	05/08/2015	3	02/08/2015	12/09/2015	38
123456	12/09/2015	4	05/08/2015	17/09/2015	5
123456	17/09/2015	5	12/09/2015

Could anyone advise please?

Thanks,

Holly

Re: If Previous, Peek problem

settu_periasamy — Fri, 09 Oct 2015 02:49:56 GMT

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;

Re: If Previous, Peek problem

tresB — Fri, 09 Oct 2015 05:02:11 GMT

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;

Re: If Previous, Peek problem

Fri, 09 Oct 2015 08:20:38 GMT

Thanks, this is exactly what I needed