Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I am having a problem with ranking data as per my needs.
I tried in SQL directly and with use of QV functions.
I want to achieve rank as per below example
Employee | Type | Week | Rank |
---|---|---|---|
A | 1 | 10 | 1 |
A | 1 | 11 | 1 |
A | 2 | 12 | 2 |
A | 1 | 13 | 3 |
B | 3 | 10 | 1 |
B | 2 | 11 | 2 |
B | 3 | 12 | 3 |
B | 2 | 13 | 4 |
B | 2 | 14 | 4 |
1. Rank should reset for next employee (row 5)
2. When Type is the same continuously, week by week, Rank stays the same. (rows 1 & 2)
2. When Type changes, rank changes as well (row 3)
3. When same Employee and Type combination is occurring again, but not as continuation, Rank must change (row 2 & 4, as was divided by different type in row 3
How to achieve that?
Regards!
Try:
LOAD
Employee,
Type,
Week,
if(Previous(Employee)=Employee,if(Previous(Type)=Type,peek(Rank),rangesum(1,peek(Rank))),1) as Rank
FROM ...
Try:
LOAD
Employee,
Type,
Week,
if(Previous(Employee)=Employee,if(Previous(Type)=Type,peek(Rank),rangesum(1,peek(Rank))),1) as Rank
FROM ...
Please see the attached file
Big thanks to both of you! Both solutions are correct Marking Gysbert's one as correct as he was faster.
Yeah gwassenaar is the champ.