topic Re: QlikView Rank in QlikView

QlikView Rank

Anonymous — Sat, 09 Dec 2017 00:48:11 GMT

I have a simple table as illustrated below.

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.

Re: QlikView Rank

maxgro — Sat, 09 Dec 2017 09:02:52 GMT

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

and then pick the first 2 Levels.

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

];

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

Re: QlikView Rank

el_aprendiz111 — Sat, 09 Dec 2017 17:00:37 GMT

Hi Jerry

Expressions:

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

Re: QlikView Rank

el_aprendiz111 — Sat, 09 Dec 2017 17:02:23 GMT

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