topic Ranking in script in QlikView

Ranking in script

danielact — Thu, 02 Feb 2012 18:38:45 GMT

I want to do some ranking in my script, by a dollar amount field. Usually it's pretty simple - just order by the Amount field, and use RowNo. However, in this case, it's a bit more complex. Let's say I have two different departments. here's how my data would look:

Dept Amount

A 5

B 7

A 10

A 1

A 6

B 4

B 20

A 15

B 12

B 8

I want to have a separate ranking for each Department - so A should have rankings 1 - 5 and B should also have 1 - 5, instead of just having 1 - 10 over the whole thing.

Any thoughts?

Ranking in script

swuehl — Thu, 02 Feb 2012 18:46:13 GMT

Maybe like this:

INPUT:

LOAD * INLINE [

Dept, Amount

A , 5

B , 7

A , 10

A , 1

A , 6

B , 4

B , 20

A , 15

B , 12

B , 8

];

Load

Dept,

Amount,

if(Recno()=1 or Dept <> peek(Dept),1,peek(Rank)+1) as Rank

Resident INPUT order by Dept, Amount desc;

drop table INPUT;

Re: Ranking in script

Thu, 02 Feb 2012 18:53:58 GMT

Hi,

Please find the attached application.

Hope it answers your question.

Ashutosh