Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi Jerry
Expressions:
=concat (IF( aggr ( Rank(SUM(VLA)),Company, Level)<=2, Level & chr(44)))
=concat (IF( aggr ( Rank(SUM([BP %])),Company, Level)<=2, Level & chr(44)))