Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

jerryhelms
New Contributor

QlikView Rank

I have a simple table as illustrated below.

Capture.PNG

Lines 14 and 15 illustrate the desired results.  I would like to rank the BP column for each Company and then display a concatenated list of the first two items as an expression in a straight table that has no dimensions.

I can do an aggr and rank function is a different table that has dimensions, but can't seem to be able to do it in a single expression.

It would be great if I could do it in a load statement, but aggr isn't supported there.

3 Replies
MVP
MVP

Re: QlikView Rank

In a script you can concatenate the Level order by BP descending and group by Company

and then pick the first 2 Levels.

S:

load * inline [

Company, Level, BP

X,A,2

X,B,5

X,C,15

X,D,15

X,E,9

Y,A,8

Y,B,1

Y,C,12

Y,D,6

Y,E,9

];

T:

LOAD

    Company,

    Left(Concat(Level, ',', -BP), Index(Concat(Level, ',', -BP), ',', 2) -1)

Resident S

Group By Company;



In a Qlikview chart (straight table) without dimensions it works if you select one Company.

In a Qlikview chart (straight table) you can also add the Company dimension and hide the dimension (Hide Column) in Presentation tab

el_aprendiz111
Valued Contributor

Re: QlikView Rank

Hi Jerry

Expressions:

=concat (IF( aggr ( Rank(SUM(VLA)),Company, Level)<=2, Level & chr(44)))


rnk.png



el_aprendiz111
Valued Contributor

Re: QlikView Rank

=concat (IF( aggr ( Rank(SUM([BP %])),Company, Level)<=2, Level & chr(44)))

Community Browser