Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I'm don't know if QlikView can solve this problem, but any guidance would be great.
I have a table that looks like this:
field1 field2
AAA | ZZZ |
AAA | YYY |
AAA | XXX |
BBB | WWW |
BBB | VVV |
BBB | UUU |
BBB | TTT |
CCC | SSS |
CCC | RRR |
CCC | QQQ |
CCC | PPP |
CCC | OOO |
CCC | NNN |
CCC | MMM |
CCC | LLL |
CCC | KKK |
I need logic that dynamically ranks so that it looks like this:
field1 field2 rank
AAA | ZZZ | 1 |
AAA | YYY | 2 |
AAA | XXX | 3 |
BBB | WWW | 1 |
BBB | VVV | 2 |
BBB | UUU | 3 |
BBB | TTT | 4 |
CCC | SSS | 1 |
CCC | RRR | 2 |
CCC | QQQ | 3 |
CCC | PPP | 4 |
CCC | OOO | 5 |
CCC | NNN | 6 |
CCC | MMM | 7 |
CCC | LLL | 8 |
CCC | KKK | 9 |
I can guarantee sort order of field1 and field2. The business needs dictate that this must happen in the script.
Any suggestions?
If the field2 is already sorted in the rank they should be as it seems to be the case here - this script should do:
The AutoNumber with the second parameter given to separate the groups of field1 will count a sequence independently resulting in the Rank you seem to need...
Resulting in this:
You'll need to use Peek() in your script.
Something along the lines of
IF (Peek('field1')=[field1], Peek('fieldctr')+1, 1) as fieldctr
fieldctr gets reset to 1 every time field1 changes.
Hope that helps.
If the field2 is already sorted in the rank they should be as it seems to be the case here - this script should do:
The AutoNumber with the second parameter given to separate the groups of field1 will count a sequence independently resulting in the Rank you seem to need...
Resulting in this:
Works like a charm. Thanks