Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to group data by week but am having a problem with one aspect of it (see below). For the waste and production data it is a simple matter of summing and then grouping by week but with stock the final figure of the week (in the below example it is 2 000 for week 23) is the figure I want to capture so that it is closing stock, however I don't want the other stock figures to be summed. And then the closing stock figure for week 23 would be the opening stock figure for week 24. I'm thinking peek is the solution however I'm not having any success with using it in a grouping scenario.
Thanks,
Ralph
Raw Table
Date | Week | Stock | Waste | Production |
---|---|---|---|---|
11/06/13 | 23 | 1000 | 1500 | 100 000 |
12/06/13 | 23 | 2000 | 2500 | 200 000 |
13/06/13 | 23 | 1000 | 1000 | 200 000 |
14/06/13 | 23 | 3000 | 500 | 400 000 |
15/06/13 | 23 | 2000 | 2000 | 500 000 |
16/06/13 | 23 | 4000 | 4000 | 200 000 |
17/06/13 | 23 | 2000 | 5000 | 300 000 |
18/06/13 | 24 | 3000 | 2000 | 150 000 |
19/06/13 | 24 | 1000 | 500 | 250 000 |
20/06/13 | 24 | 4000 | 1000 | 50 000 |
21/06/13 | 24 | 5000 | 2500 | 500 000 |
22/06/13 | 24 | 2000 | 500 | 150 000 |
23/06/13 | 24 | 2000 | 1500 | 250 000 |
24/06/13 | 24 | 3000 | 5000 | 20 000 |
Expected Table
Week | Stock | Waste | Production |
---|---|---|---|
23 | 2 000 | 16 500 | 1 900 000 |
24 | 3 000 | 13 000 | 1 370 000 |
This script appears to work:
RAW_Data:
LOAD * INLINE [
Date,Week,Stock,Waste,Production
11/06/13,23,1000,1500,100000
12/06/13,23,2000,2500,200000
13/06/13,23,1000,1000,200000
14/06/13,23,3000,500,400000
15/06/13,23,2000,2000,500000
16/06/13,23,4000,4000,200000
17/06/13,23,2000,5000,300000
18/06/13,24,3000,2000,150000
19/06/13,24,1000,500,250000
20/06/13,24,4000,1000,50000
21/06/13,24,5000,2500,500000
22/06/13,24,2000,500,150000
23/06/13,24,2000,1500,250000
24/06/13,24,3000,5000,20000];
NewData:
LOAD Date,Week,Stock,Waste,Production,IF(PEEK('Week',RowNo(),'RAW_Data')=Week,NULL(),Stock) AS ClosingStock RESIDENT RAW_Data ORDER BY Date;
DROP TABLE RAW_Data;
FinalTable:
LOAD Week,Sum(ClosingStock) AS Stock, Sum(Waste) AS Waste, Sum(Production) AS Production RESIDENT NewData GROUP BY Week;
DROP TABLE NewData;
This script appears to work:
RAW_Data:
LOAD * INLINE [
Date,Week,Stock,Waste,Production
11/06/13,23,1000,1500,100000
12/06/13,23,2000,2500,200000
13/06/13,23,1000,1000,200000
14/06/13,23,3000,500,400000
15/06/13,23,2000,2000,500000
16/06/13,23,4000,4000,200000
17/06/13,23,2000,5000,300000
18/06/13,24,3000,2000,150000
19/06/13,24,1000,500,250000
20/06/13,24,4000,1000,50000
21/06/13,24,5000,2500,500000
22/06/13,24,2000,500,150000
23/06/13,24,2000,1500,250000
24/06/13,24,3000,5000,20000];
NewData:
LOAD Date,Week,Stock,Waste,Production,IF(PEEK('Week',RowNo(),'RAW_Data')=Week,NULL(),Stock) AS ClosingStock RESIDENT RAW_Data ORDER BY Date;
DROP TABLE RAW_Data;
FinalTable:
LOAD Week,Sum(ClosingStock) AS Stock, Sum(Waste) AS Waste, Sum(Production) AS Production RESIDENT NewData GROUP BY Week;
DROP TABLE NewData;
Perfect that works great.
Thanks,
Ralph