Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank in script

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

1 Solution

Accepted Solutions
Kushal_Chawda

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;



View solution in original post

3 Replies
Kushal_Chawda

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;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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 ;