Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a simple table, which I would like to calculate the avg gap for each customer.
For CustomerID 1, year of purchased is 2020, 1999, 1997.
The gaps purchased is:
1 (2000-1999)
2 (1999-1997)
Avg (1+2) = 1.5
For Customer 4, year of purchased is 2020, 1995, 1988
The gaps of purchase is:
25 (2020 - 1995)
7 (1995 - 1988)
Avg (25+7) = 16
How should the script like to achieve the above calculation?
Sample Table
[CustomerRecords]:
LOAD * INLINE [
CustomerID, PurchaseDate
1, 1/1/2020
2, 1/1/2020
3, 1/1/2020
4, 1/1/2020
5, 1/1/2020
1, 1/1/1999
2, 1/1/1998
3, 1/1/1990
4, 1/1/1995
1, 1/1/1997
4, 1/1/1988
];
Your scenario given for CustomerID 1 is 2000, but sample data shows 2020.
I used the sample data given and the result shows:
Script:
[CustomerRecords]:
LOAD CustomerID, PurchaseDate,Year(dATE#(PurchaseDate,'DD/MM/YYYY'))as Year INLINE [
CustomerID, PurchaseDate
1, 1/1/2020
2, 1/1/2020
3, 1/1/2020
4, 1/1/2020
5, 1/1/2020
1, 1/1/1999
2, 1/1/1998
3, 1/1/1990
4, 1/1/1995
1, 1/1/1997
4, 1/1/1988
];
NoConcatenate
tempData:
load *,if(RowNo()=1,null(),if(peek(CustomerID)=CustomerID,peek(Year)-Year,null())) as YearDiff resident CustomerRecords
order by CustomerID,PurchaseDate desc
;
Avg:
load CustomerID,avg(YearDiff) as AvgDiff
resident tempData
group by CustomerID
;
drop table CustomerRecords;
Your scenario given for CustomerID 1 is 2000, but sample data shows 2020.
I used the sample data given and the result shows:
Script:
[CustomerRecords]:
LOAD CustomerID, PurchaseDate,Year(dATE#(PurchaseDate,'DD/MM/YYYY'))as Year INLINE [
CustomerID, PurchaseDate
1, 1/1/2020
2, 1/1/2020
3, 1/1/2020
4, 1/1/2020
5, 1/1/2020
1, 1/1/1999
2, 1/1/1998
3, 1/1/1990
4, 1/1/1995
1, 1/1/1997
4, 1/1/1988
];
NoConcatenate
tempData:
load *,if(RowNo()=1,null(),if(peek(CustomerID)=CustomerID,peek(Year)-Year,null())) as YearDiff resident CustomerRecords
order by CustomerID,PurchaseDate desc
;
Avg:
load CustomerID,avg(YearDiff) as AvgDiff
resident tempData
group by CustomerID
;
drop table CustomerRecords;