Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

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

Community Browser