topic Re: How to rank customers within groups in QlikView

How to rank customers within groups

Anonymous — Tue, 12 May 2015 14:21:29 GMT

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.

Re: How to rank customers within groups

jonathandienst — Tue, 12 May 2015 14:28:08 GMT

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

Re: How to rank customers within groups

Anonymous — Tue, 12 May 2015 14:50:31 GMT

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

sunny_talwar — Tue, 12 May 2015 14:54:51 GMT

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

Re: How to rank customers within groups

jonathandienst — Tue, 12 May 2015 15:03:57 GMT

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

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

Re: How to rank customers within groups

anbu1984 — Tue, 12 May 2015 15:33:26 GMT

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;