Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ashmeetthukral
New Contributor II

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.

Tags (1)
5 Replies
MVP
MVP

Re: How to rank customers within groups

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
ashmeetthukral
New Contributor II

Re: How to rank customers within groups

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

Re: How to rank customers within groups

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

MVP
MVP

Re: How to rank customers within groups

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
Honored Contributor III

Re: How to rank customers within groups

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;

Community Browser