Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank by Name & amount in script

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
2A2001
3A1402
1A1003
6B2001
5B1302
4B120

3

Note that I want to do this in script

1 Solution

Accepted Solutions
Kushal_Chawda

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;


View solution in original post

4 Replies
Kushal_Chawda

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;


sunny_talwar

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;


Capture.PNG

Not applicable
Author

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

Not applicable
Author

Thank you.. Both working fine