If Previous, Peek problem

[#1]

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

[settu_periasamy]

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;

[tresesco · Accepted solution]

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; 

[#4]

Thanks, this is exactly what I needed