Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
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.

manucamon
Specialist III
Specialist III

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

manucamon
Specialist III
Specialist III

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

Regards!!