7 Replies Latest reply: Nov 15, 2015 6:19 AM by Marcel Hug

# how to rank item within groups

Hi,

I am trying to rank item within a group, see below example, add a new column Rank to count the order of Cat_2 which in the same group of Cat_1. Then I can just select rank=1, to get a 1 to1 mapping table between Cat_1 and Cat_2

Thanks a lot!

 INPUT Cat_1 Cat_2 1 a 1 d 1 b 2 f 2 j 2 k 3 n 3 m 3 l 3 s ... ... EXPECTED OUTPUT Cat_1 Cat_2 Rank 1 a 1 1 b 2 1 d 3 2 f 1 2 j 2 2 k 3 3 l 1 3 m 2 3 n 3 3 s 4 ... ... ...
• ###### Re: how to rank item within groups

*,

AutoNumber(Cat_1&Cat_2,Cat_1) as Rank

Inline

[

Cat_1, Cat_2

1, a

1, d

1, b

2, f

2, j

2, k

3, n

3, m

3, l

3, s

];

• ###### Re: how to rank item within groups

That's a clever use of AutoNumber()!

• ###### Re: how to rank item within groups

Hi

If you are ranking by load order in the script. then something like this should work:

Cat_2,

If(Cat_1 = Previous(Cat_1), Peek(Rank) + 1, 1) As Rank

FROM .....

Order By Cat_1;

If you need to order by value in Cat_2, for example, add this to the order by:

Order By Cat_1, Cat_2;

HTH

Jonathan

• ###### Re: how to rank item within groups

Try like:

AutoNumber(RecNo(), Cat_1) as Rank

• ###### Re: how to rank item within groups

Hi...

I know its already solved but i figured out one more way to do it.

I created an expression in a straight table as

=rank(-ord(Cat_3))

• ###### Re: how to rank item within groups

brilliant idea, Shashi! Tks

• ###### Re: how to rank item within groups

Hi

see my example file rank.qvw.

- it contains a sub providing flexible ranking mechanisms,
such as ranking by autonumber or by rowno().
- ranks can be split into half ranks for identical values
(e.g. rank 11,12,13 -> 12, 12, 12)

https://community.qlik.com/docs/DOC-13949

Regards - Marcel