Assigning Rank in script

[hitha1512]

Hi Team,

Trying to get the below output(Rank) based on Sum(Sales) at the script level. 

Sale ID	Sales	Sum(Sales)	Rank
1	20	20	1
2	20	20	1
3	40	40	2
4	50	50	3
5	50	50	3
6	70	70	4
7	80	80	5

Regards,

Hitha Dhani

[justISO · Accepted solution]

Hi, maybe something like this can be used, if your_table already has aggregated sales per [Sale ID]:

Final:
LOAD *,
AutoNumber(Sales) as Rank
RESIDENT your_table
ORDER BY Sales asc;

DROP TABLE your_table;

[brunobertels · Accepted solution]

Hi 

May be like this 

[Table]:

LOAD * INLINE
[
Sale ID,Sales,Sum(Sales),Rank
1,20,20,1
2,20,20,1
3,40,40,2
4,50,50,3
5,50,50,3
6,70,70,4
7,80,80,5
](delimiter is ',');

Rank:
load *,

autonumber(Sales) as Rank_Number

Resident Table order by [Sales] ;
drop table Table;

[Iswarya_]

Hi @hitha1512 ,

Please try by the below script:

Temp:
Load * Inline [
SaleID, Sales
1, 20
2, 20
3, 40
4, 50
5, 50
6, 70
7, 80
];

NoConcatenate
Final:
Load SaleID,
Sum(Sales) as Sales
resident Temp
Group by SaleID;
drop table Temp;

NoConcatenate
Final_1:
Load

SaleID,
Sales,
if(RecNo()=1,1,
      IF(Sales=Previous(Sales),Peek(Rank),
             IF(Sales<>Previous(Sales),Peek(Rank)+1))) as Rank
resident Final
Order by Sales;
drop table Final;

EXIT SCRIPT;