Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping Data (with peek)

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

DateWeekStockWasteProduction
11/06/13

23

1000

1500100 000

12/06/13

2320002500200 000
13/06/132310001000200 000
14/06/13233000500400 000
15/06/132320002000500 000
16/06/132340004000200 000
17/06/132320005000300 000
18/06/132430002000150 000
19/06/13241000500250 000
20/06/13

24

4000100050 000
21/06/132450002500500 000
22/06/13242000500150 000
23/06/132420001500250 000
24/06/13243000500020 000

Expected Table

WeekStockWasteProduction
232 00016 5001 900 000
243 00013 0001 370 000
1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

2 Replies
Not applicable
Author

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;

Not applicable
Author

Perfect that works great.

Thanks,


Ralph