9 Replies Latest reply: Jul 14, 2016 10:16 AM by Sunny Talwar RSS

    List Top 2 Record Per Group

    Christopher Ellis

      I am fairly new to QlikSense and have found it a very powerful tool.

       

      However I am struggling a bit to put the correct syntax together to list records in order and then pick the top 2 from that group then move on to the next Group.

       

      From what I have read it seems to have something to do with the functions peek and previous but I cant seem to get it right.

       

      Any help or advise would be much appreciated.

      Sorted Table

      Group 1 Group 2Value
      AA14
      AA23
      AA31
      BA16
      BA25
      BA33

       

      The result I am looking for is

      Group 1Group 2Value
      AA14
      AA23
      BA16
      BA25
        • Re: List Top 2 Record Per Group
          Vineeth Pujari

          do you need this in a straight table?

          • Re: List Top 2 Record Per Group
            Sunny Talwar

            May be try this:

             

            Table:

            LOAD [Group 1],

                [Group 2],

                Value

            FROM

            [https://community.qlik.com/thread/224729]

            (html, codepage is 1252, embedded labels, table is @1);

             

            FinalTable:

            NoConcatenate

            LOAD [Group 1],

                [Group 2],

                Value

            Where RowNo <= 2;

            LOAD *,

              AutoNumber(Value, [Group 1]) as RowNo

            Resident Table

            Order By [Group 1], Value desc;

             

            DROP Table Table;


            Capture.PNG

              • Re: List Top 2 Record Per Group
                Christopher Ellis

                Hi Sunny,

                 

                Many thanks it works a treat.

                 

                One thing I am coming across is when the values are the same for the group it brings in both. So if A1 and A2 had a 4 it would bring in both when I only need 1 of them. I don't mind which one it is.

                 

                Is there a way of only bringing in only one of the values when they are the same from the code you provided.

                 

                Thanks

                 

                Chris

                  • Re: List Top 2 Record Per Group
                    Sunny Talwar

                    And bring 1 as second max like this?

                     

                    Capture.PNG

                     

                    Script:

                    Table:

                    LOAD [Group 1],

                      FirstValue([Group 2]) as [Group 2],

                      Value

                    Group By [Group 1], Value;

                    LOAD * INLINE [

                        Group 1, Group 2, Value

                        A, A1, 4

                        A, A2, 4

                        A, A3, 1

                        B, A1, 6

                        B, A2, 5

                        B, A3, 3

                    ];

                     

                    FinalTable:

                    NoConcatenate

                    LOAD [Group 1],

                        [Group 2],

                        Value

                    Where RowNo <= 2;

                    LOAD *,

                      AutoNumber(Value, [Group 1]) as RowNo

                    Resident Table

                    Order By [Group 1], Value desc;

                     

                    DROP Table Table;

                      • Re: List Top 2 Record Per Group
                        Christopher Ellis

                        Hi Sunny,

                         

                        The code below I have adapted using your example and is spot on. The idea you have explained is exactly what I am looking for but cant quite figure out how to adapt it into the code below.


                        Any advice appreciated.

                         

                        LOAD
                            "Primary",
                            "Subject",
                            "Resultset",
                            "PU",
                            "Basket",
                            "Category name",
                            "P8 Points"   
                        Resident Assessment
                        Where ((([Category name])='Assessment') AND ((Basket)=1));

                        Basket1:
                        NoConcatenate
                        LOAD [Primary],
                          [Subject],
                             [Resultset],
                             [PU],
                             [Basket],
                             [P8 Points],
                             [P8 Points] As "Basket 1"
                        Where RowNo < 2;
                        LOAD *,
                          AutoNumber([P8 Points],[Primary]&[PU]&[Basket]) as RowNo
                        Resident TMP
                        Order By [Primary],[Subject],[Resultset],[PU],[Basket],[P8 Points] desc;

                        DROP Table TMP;

                         

                        Assessment

                        PrimarySubjectResultsetPUBasketCatergoty nameP8 Points
                        A1EnglishEnglish Data Collection 1Y11-Pu11Assessment5
                        A1English LitEnglish Lit Data Collection 1Y11-Pu11Assessment5
                        A1MathsMaths Data Collection 1Y11-Pu12Assessment3
                        A1FrenchFrench Data Collection 1Y11-Pu13Assessment2
                        A2EnglishEnglish Data Collection 1Y11-Pu11Assessment4
                        A2English LitEnglish Lit Data Collection 1Y11-Pu11Assessment5
                        A2MathsMaths Data Collection 1Y11-Pu12Assessment3
                        A2FrenchFrench Data Collection 1Y11-Pu13Assessment2