Adding a counter by grouping multiple dimensions in Qlik Sense script

[abhishek2]

Hi All,

I  need to add counter using multiple dimensions during script load. Please see the data below:

A	B	C	Counter
AA	11	1	1
AA	12	2	2
BB	21	4	1
BB	22	5	2
BB	23	6	3

I need to use the counter column to then filter for the top N by Column A. I have tried using AutoNumber(), however, am not able to restart the counter by column A as dimension.

Any help will be appreciated. Thank you!

Regards, Abhishek.

[lironbaram · Accepted solution]

hi 

your script should be something like :

assuming you already load the table let call it Table1

than your script will be 

Table2:

load *,

          if(previous(A)=A, rangesum(peek('Counter'),1),1) as Counter

resident Table1

order by A;

drop table Table1;

[abhishek2]

Thanks you Lironbaram!

Was able to resolve this.

One complexity that I did not highlight was that Column C had repeated values. For example, there can values of A which have the same rank of C. See below:

AA,11,1

AA,12,1

AA,13,2

BB,22,5

BB,23,6

Following is the solution that I used for resolving this issue. Created a rank column and then created a counter column.

Load *,

AutoNumber(Row(), A) as Counter;

Load *,

IF(A <> Previous(A),1,IF(C <> Previous(C), Peek(Rank) + 1, Peek(Rank))) as Rank

Resident Table1

Order By A, C asc;

Regards, Abhishek.