Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to rank customers within groups

Hi,

I want to populate rank within groups. Please help.

For example, this is what my data looks like:

 

Customer IDDepartmentRevenue
1A10
1B12
1C13
1D14
1E15
2B6
2C7
2D8
2E9

I want to populate rank as shown below for each customer:

 

Customer IDDepartmentRevenueRank
1A101
1B122
1C133
1D144
1E155
2B61
2C72
2D83
2E94

I want to perform this action in Qlikview script and my data is in qvd format.

Thanks in advance for the answers.

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
anbu1984
Master III
Master III

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;