Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

danielact
Not applicable

Ranking in script

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?

1 Solution

Accepted Solutions
swuehl
Not applicable

Ranking in script

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;

2 Replies
swuehl
Not applicable

Ranking in script

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;

Not applicable

Re: Ranking in script

Hi,

Please find the attached application.

Hope it answers your question.

..

Ashutosh