Count rows per group

[#1]

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

I

[Gysbert_Wassenaar]

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;

[#3]

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.

[Gysbert_Wassenaar]

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

[#5]

Why the column IdDim3 doesn´t show all results?

[Gysbert_Wassenaar]

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.

[#7]

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

[Gysbert_Wassenaar · Accepted solution]

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

[#9]

Very thanks.