Rank in script

[#1]

I have a data like below

LOAD * Inline

[
Product,ProductLine, Sales
A,1, 100,
A,2,200
B,1,150
B,2, 170
C,1,140
C,200 ] ;

I need the output like below

Product  Rank

A               2

B               1

C               3

[Kushal_Chawda · Accepted solution]

try like this

t1:

LOAD * Inline [

Product,ProductLine, Sales

A,1, 100,

A,2,200

B,1,150

B,2, 170

C,1,140

C,200 ];

left Join

LOAD Distinct Product,

sum(Sales) as SalesByProduct

Resident t1

Group by Product;

left Join

LOAD Distinct Product,

AutoNumber(SalesByProduct,'Product') as SalesRankByProduct

Resident t1

Order by SalesByProduct desc;

[Gysbert_Wassenaar]

Input:

LOAD * Inline

[
Product,ProductLine, Sales
A,1, 100,
A,2,200
B,1,150
B,2, 170
C,1,140
C,200 ] ;

Temp:

LOAD Product, Sum(Sales) As SumSales

Resident Input

Group By Product;

Result:

LOAD Product, RecNo() as Rank

Resident Temp

Order By SumSales desc;

Drop Table Temp;

[Anonymous]

Or maybe like this.  Depends on whether you are after a condensed summary table or one that retains the details data.

Temp1 :
LOAD * Inline

[

Product,ProductLine, Sales

A,1, 100,

A,2,200

B,1,150

B,2, 170

C,1,140

C,200 ] ;

Temp2 :

load

Product ,

Sum(Sales) as [SalesSum]

resident Temp1

group by Product

order by Product

;

drop table Temp1 ;

Data :

load

rowno() as Rank ,

Product ,

SalesSum

resident Temp2

order by SalesSum desc

;

drop table Temp2 ;