topic Re: Rank in script in QlikView

Rank in script

Sat, 28 Nov 2015 09:58:32 GMT

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

Re: Rank in script

Kushal_Chawda — Sat, 28 Nov 2015 10:05:03 GMT

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;

Re: Rank in script

Gysbert_Wassenaar — Sat, 28 Nov 2015 10:08:54 GMT

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;

Re: Rank in script

Anonymous — Sat, 28 Nov 2015 10:10:56 GMT

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 ;