Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to populate rank within groups. Please help.
For example, this is what my data looks like:
Customer ID | Department | Revenue |
1 | A | 10 |
1 | B | 12 |
1 | C | 13 |
1 | D | 14 |
1 | E | 15 |
2 | B | 6 |
2 | C | 7 |
2 | D | 8 |
2 | E | 9 |
I want to populate rank as shown below for each customer:
Customer ID | Department | Revenue | Rank |
1 | A | 10 | 1 |
1 | B | 12 | 2 |
1 | C | 13 | 3 |
1 | D | 14 | 4 |
1 | E | 15 | 5 |
2 | B | 6 | 1 |
2 | C | 7 | 2 |
2 | D | 8 | 3 |
2 | E | 9 | 4 |
I want to perform this action in Qlikview script and my data is in qvd format.
Thanks in advance for the answers.
This will do it (assuming you have loaded the data into [Data]:
LOAD [Customer ID],
Department,
Revenue,
If ([Customer ID] <> Previous([Customer ID]), 1, Peek(Rank) + 1) As Rank
Resident Data
ORDER BY CustomerID, Revenue
Thanks for the reply. It's not working, I got the response: Garbage after expression.
Can you please help me identify the problem in the code?
I wrote this code:
file_cust:
LOAD Territory,
[Member Card Number],
[Transaction Concept],
[Revenue AED],
(If ([Member Card Number] <> Previous([Member Card Number]), 1, Peek(Rank) + 1)) As Rank,
'Y' as file_cust
FROM SOURCE
where [# of Invoices] >0
and [Revenue AED] >0
and [Member Card Number] > 0
order by [Member Card Number],[Revenue AED] ;
See if this work:
file_cust:
LOAD Territory,
[Member Card Number],
[Transaction Concept],
[Revenue AED],
(If ([Member Card Number] <> Previous('Member Card Number'), 1, Peek(Rank) + 1)) As Rank,
'Y' as file_cust
FROM SOURCE
where [# of Invoices] >0
and [Revenue AED] >0
and [Member Card Number] > 0
order by [Member Card Number],[Revenue AED];
Its possible that you don't need field delimiters in a Previous() expression in script:
...Previous(Member Card Number),...
T1:
Load * Inline [
CustomerID,Department,Revenue
1,A,10
1,B,12
1,C,13
1,D,14
1,E,15
2,B,6
2,C,7
2,D,8
2,E,9 ];
T2:
Load *,AutoNumber(RowNo(),CustomerID) As Rank Resident T1 Order By CustomerID,Revenue;
DROP Table T1;