Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have a set of historical inventory and I am trying to calculate the age of inventory for each product resetting to 0 each time the
inventory reaches 0.
Date, Product, Inventory are already in a table Exposure I loaded, and I am trying to add the Age column to it.
From my research, it seems like I should Iterate with a condition that the previous Inventory for each Product is not null, but I am lost in the process.
Thanks and regards
You just need to sort your table by Product and Date and then use peek() function, e.g. along these lines
Set DateFormat = 'DD/MM/YYYY';
INPUT:
LOAD * INLINE [
Date, Product, Inventory
03/12/2012,A,0
03/12/2012,B,10
03/12/2012,C,2
04/12/2012,A,0
04/12/2012,B,10
04/12/2012,C,2
05/12/2012,A,1
05/12/2012,B,5
05/12/2012,C,0
06/12/2012,A,2
06/12/2012,B,5
06/12/2012,C,1
07/12/2012,A,2
07/12/2012,B,5
07/12/2012,C,1
];
RESULT:
LOAD Date, Product, Inventory,
if(peek(Product) = Product and Inventory>0, rangesum(peek(Age),1),if(Inventory>0,1,0)) as Age
Resident INPUT order by Product, Date;
drop table INPUT;
Hope this helps,
Stefan
P.S.
Posting some sample data is great! But it would be even better if you've posted it as text, so we are able to copy paste it, not as bitmap image (or even better formatted as INLINE LOAD or in sample qvw).
You just need to sort your table by Product and Date and then use peek() function, e.g. along these lines
Set DateFormat = 'DD/MM/YYYY';
INPUT:
LOAD * INLINE [
Date, Product, Inventory
03/12/2012,A,0
03/12/2012,B,10
03/12/2012,C,2
04/12/2012,A,0
04/12/2012,B,10
04/12/2012,C,2
05/12/2012,A,1
05/12/2012,B,5
05/12/2012,C,0
06/12/2012,A,2
06/12/2012,B,5
06/12/2012,C,1
07/12/2012,A,2
07/12/2012,B,5
07/12/2012,C,1
];
RESULT:
LOAD Date, Product, Inventory,
if(peek(Product) = Product and Inventory>0, rangesum(peek(Age),1),if(Inventory>0,1,0)) as Age
Resident INPUT order by Product, Date;
drop table INPUT;
Hope this helps,
Stefan
P.S.
Posting some sample data is great! But it would be even better if you've posted it as text, so we are able to copy paste it, not as bitmap image (or even better formatted as INLINE LOAD or in sample qvw).
Many thanks.
It works perfect.
Sorry for the image format of the table, I tried to copy and paste but it looked awful.
Best