Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
Thanks all. I will apply the same on actual data and will let you know the same
Thank you.. Both working fine