3 Replies Latest reply: Dec 9, 2017 12:02 PM by Fer Fer RSS

    QlikView Rank

    Jerry Helms

      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.

        • Re: QlikView Rank
          Massimo Grossi

          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

          • Re: QlikView Rank
            Fer Fer

            Hi Jerry

             

            Expressions:

             

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


            rnk.png