how to rank item within groups

[#1]

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

Can I fulfil this in a loading script?

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
...	...	...

[MK_QSL · Accepted solution]

Load

  *,

  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

];

[jonathandienst]

Hi

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

     LOAD Cat_1,

          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

[tresesco]

Try like:

Load *,

          AutoNumber(RecNo(), Cat_1) as Rank

[jonathandienst]

That's a clever use of AutoNumber()!

[#6]

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))

[#7]

brilliant idea, Shashi! Tks

[hugmarcel]

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