topic Re: Rank by Name & amount in script in QlikView

Rank by Name & amount in script

Sun, 27 Mar 2016 11:59:05 GMT

Hi All,

I have sample data set like this

LOAD * Inline [

ID, Name, Amount

1, A,100

2, A,200

3, A,140

4, B, 120

5, B, 130

6,B, 200 ];

I need the output like below

ID	Name	Amount	Rank
2	A	200	1
3	A	140	2
1	A	100	3
6	B	200	1
5	B	130	2
4	B	120	3

Note that I want to do this in script

Re: Rank by Name & amount in script

Kushal_Chawda — Sun, 27 Mar 2016 12:02:02 GMT

try like this

Data:
LOAD * Inline [
ID, Name, Amount
1, A,100
2, A,200
3, A,140
4, B, 120
5, B, 130
6,B, 200 ];

New:
NoConcatenate
LOAD *,
if(Name= Previous(Name), Peek('Rank')+1,1) as Rank
Resident Data
Order by Name,Amount desc;

DROP Table Data;

Re: Rank by Name & amount in script

sunny_talwar — Sun, 27 Mar 2016 12:04:35 GMT

Try this script:

Table:

LOAD * Inline [

ID, Name, Amount

1, A, 100

2, A, 200

3, A, 140

4, B, 120

5, B, 130

6, B, 200

];

FinalTable:

LOAD *,

If(Name = Peek('Name'), RangeSum(Peek('Rank'), 1), 1) as Rank

Resident Table

Order By Name, Amount desc;

DROP Table Table;

Re: Rank by Name & amount in script

Sun, 27 Mar 2016 12:23:31 GMT

Thanks all. I will apply the same on actual data and will let you know the same

Re: Rank by Name & amount in script

Sun, 27 Mar 2016 12:52:56 GMT

Thank you.. Both working fine