How to rank customers within groups

[Anonymous]

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.

[jonathandienst]

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

[Anonymous]

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] ;

[sunny_talwar]

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];

[jonathandienst]

Its possible that you don't need field delimiters in a Previous() expression in script:

          ...Previous(Member Card Number),...

[anbu1984]

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;