Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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;