Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I would like to count the row count of the distinct value like the following table:
ID Count
A 1
A 2
A 3
A 4
B 1
B 2
B 3
C 1
C 2
C 3
D 1
I have googled abt RangeCount function but I don't think it can serve my purpose.
Is there any method I can use for this calculation?
Thanks
Felix
Hi,
Try this code:
Temp:
LOAD * INLINE [
ID
A
A
A
A
B
B
B
C
C
C
D
];
Final:
LOAD
ID,
if(RowNo()=0 or ID<>Peek(ID),1,Peek(Count)+1) as Count
Resident Temp
Order by ID;
Drop Table Temp;
Or like this
AutoNumber(RowNo(), ID) as Count