Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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 ;