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: 
danielact
Partner - Creator III
Partner - Creator III

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

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;

View solution in original post

2 Replies
swuehl
MVP
MVP

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

Hi,

Please find the attached application.

Hope it answers your question.

..

Ashutosh