topic Re: how to rank item within groups in QlikView

how to rank item within groups

Tue, 24 Feb 2015 12:39:45 GMT

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

Re: how to rank item within groups

MK_QSL — Tue, 24 Feb 2015 12:45:42 GMT

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

];

Re: how to rank item within groups

jonathandienst — Tue, 24 Feb 2015 12:46:43 GMT

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

Re: how to rank item within groups

tresB — Tue, 24 Feb 2015 12:47:30 GMT

Try like:

Load *,

AutoNumber(RecNo(), Cat_1) as Rank

Re: how to rank item within groups

jonathandienst — Tue, 24 Feb 2015 13:02:52 GMT

That's a clever use of AutoNumber()!

Re: how to rank item within groups

Wed, 25 Feb 2015 04:51:41 GMT

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

Thu, 05 Mar 2015 05:11:39 GMT

brilliant idea, Shashi! Tks

Re: how to rank item within groups

hugmarcel — Sun, 15 Nov 2015 11:19:55 GMT

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