topic Re: Grouping Data (with peek) in QlikView

Grouping Data (with peek)

Mon, 24 Jun 2013 13:53:43 GMT

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

Re: Grouping Data (with peek)

Mon, 24 Jun 2013 14:19:08 GMT

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;

Re: Grouping Data (with peek)

Mon, 24 Jun 2013 15:20:50 GMT

Perfect that works great.

Thanks,

Ralph