Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Hi,
Please find the attached application.
Hope it answers your question.
..
Ashutosh