Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
didierodayo
Partner - Creator III
Partner - Creator III

RANK and Store in QVD

Hello,

I would like to rank the following table where the highest sale is ranked 1. then I ill store the table into qvd with a new field Ranking.

  

StoreSales
A3000
B2900
C756
D1645
E3680
F985

Thanks You.

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Didier,

Try this,

Data:
LOAD * INLINE [
Store, Sales
A, 3000
B, 2900
C, 756
D, 1645
E, 3680
F, 985
G, 985
]
;

Sales:
LOAD Store,
Sum(Sales) as OverAllSales
Resident Data Group By Store;

NoConcatenate
Rank:
LOAD *,
If(Isnull(Peek('Store')), 1, If(OverAllSales < Peek('OverAllSales'), Peek('Rank')+1,  Peek('Rank'))) As Rank
Resident Sales
Order By OverAllSales desc;

Drop Table Sales, Data;

Store Rank into Rank.qvd (qvd);

Note: Script Updated

View solution in original post

19 Replies
qlikview979
Specialist
Specialist

Hi

If you want front end 

Dimension:-Store

Expression:-Rank(Sales)

tyagishaila
Specialist
Specialist

TAB:

LOAD * INLINE [

Store, Sales

A, 3000

B, 2900

C, 756

D, 1645

E, 3680

F, 985

];

TAB1:

LOAD *, RowNo() as Rank

Resident TAB

order by Sales asc;

Store TAB1 into Rank.qvd(qvd);

Drop Table TAB;

tyagishaila
Specialist
Specialist

For front end,

Add calculated Dimension: Store&Sales

Expression: Store, Sale and Rank(Sum(Sales))

make Store&Sales dimension invisible (Presentation Tab --> Hide Column)

qlikview979
Specialist
Specialist

Hi,May be help full

Try this

T1:

load * Inline [

Store, Sales

A, 3000

B, 2900

C, 756

D, 1645

E, 3680

F, 985

];

NoConcatenate

T2:

load *,

RowNo() as rank,

Resident T1 order by Sales desc;

DROP table T1;


Store T2 into ABC.qvd;



tamilarasu
Champion
Champion

Hi Didier,

Try this,

Data:
LOAD * INLINE [
Store, Sales
A, 3000
B, 2900
C, 756
D, 1645
E, 3680
F, 985
G, 985
]
;

Sales:
LOAD Store,
Sum(Sales) as OverAllSales
Resident Data Group By Store;

NoConcatenate
Rank:
LOAD *,
If(Isnull(Peek('Store')), 1, If(OverAllSales < Peek('OverAllSales'), Peek('Rank')+1,  Peek('Rank'))) As Rank
Resident Sales
Order By OverAllSales desc;

Drop Table Sales, Data;

Store Rank into Rank.qvd (qvd);

Note: Script Updated

qlikview979
Specialist
Specialist

Hi Bro,

After " F" i am adding "G,985" but Rank is not showing properly why ?

Regards,

Mahesh.

Anonymous
Not applicable

Hi mahesh,

Have you tried my code?

It works adding this.

Regards!

qlikview979
Specialist
Specialist

Hi Manuel Capella,

This Question was not posted by me.  but After " F" i am adding "G,985" Rank is showing  same to  F&G.

Regards,

Mahesh

Anonymous
Not applicable

I know, but I think that it was interesting for you, because you was testing Tamil's answer.

Regards!!