topic Re: Count rows per group in QlikView

Count rows per group

Fri, 15 Feb 2013 10:06:54 GMT

Hi,

I need to count the rows per group as shown below. How can I do this?

Id	IdDim2	IdDim3	Row
A	10	2013	1
B	10	2012	1
B	11	2012	2
C	10	2011	1
D	12	2013	1
D	13	2013	2
D	15	2013	3
D	22	2011	4
E	11	2012	1
E	41	2013	2

Re: Count rows per group

Gysbert_Wassenaar — Fri, 15 Feb 2013 12:14:10 GMT

Like this:

Temp:

load Id, IdDim2, IdDim3

from ...mysource...;

Result:

load Id, IdDim2, IdDim3,

if(Id=previous(Id),rangesum(1,peek(Row))) as Row

resident Temp

order by Id, IdDim2;

Re: Count rows per group

Fri, 15 Feb 2013 12:21:13 GMT

Thanks for the reply, but I need it on a straigh table, because the classification may change dynamically, may be by id, iddim2, iddim3 or id, iddim3, iddim2.

Re: Count rows per group

Gysbert_Wassenaar — Fri, 15 Feb 2013 12:26:11 GMT

aggr(RowNo(),Id,IdDim2) for your example. Change the dimensions rowno() should be aggregated over if you use other dimensions.

Re: Count rows per group

Fri, 15 Feb 2013 13:08:04 GMT

Why the column IdDim3 doesn´t show all results?

Re: Count rows per group

Gysbert_Wassenaar — Fri, 15 Feb 2013 13:23:38 GMT

Because in your data, unlike in the sample data in your first post, there is only one unique combination of Id and IdDim2 for B: B, 10. In your first post you have B,10 and B,11.

Re: Count rows per group

Fri, 15 Feb 2013 13:36:24 GMT

I changed it for another test, when added to iddim3 I could not count rows.

Re: Count rows per group

Gysbert_Wassenaar — Fri, 15 Feb 2013 16:11:51 GMT

See attached example. There's a hidden calculated dimension that makes this work.

Re: Count rows per group

Fri, 15 Feb 2013 16:38:43 GMT

Very thanks.