Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

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

Can I fulfil this in a loading script?

Thanks a lot!

INPUT
Cat_1Cat_2
1a
1d
1b
2f
2j
2k
3n
3m
3l
3s
......
EXPECTED OUTPUT
Cat_1Cat_2Rank
1a1
1b2
1d3
2f1
2j2
2k3
3l1
3m2
3n3
3s4
.........
Tags (6)
1 Solution

Accepted Solutions
MVP
MVP

Re: how to rank item within groups

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

];

7 Replies
MVP
MVP

Re: how to rank item within groups

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

];

MVP
MVP

Re: how to rank item within groups

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: how to rank item within groups

Try like:

Load *,

          AutoNumber(RecNo(), Cat_1) as Rank

MVP
MVP

Re: how to rank item within groups

That's a clever use of AutoNumber()!

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

Not applicable

Re: how to rank item within groups

brilliant idea, Shashi! Tks

hugmarcel
Contributor III

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