Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
.........
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

];

View solution in original post

7 Replies
MK_QSL
MVP
MVP

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
Partner - Champion III
Partner - Champion III

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
tresesco
MVP
MVP

Try like:

Load *,

          AutoNumber(RecNo(), Cat_1) as Rank

jonathandienst
Partner - Champion III
Partner - Champion III

That's a clever use of AutoNumber()!

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

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
Author

brilliant idea, Shashi! Tks

hugmarcel
Specialist
Specialist

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