5 Replies Latest reply: Aug 1, 2016 4:22 PM by Sunny Talwar RSS

    Count unique

    Britney Fields

      Hi Everyone,

       

      Let's say I have the following dataset. What expression can I use in qlikview to count the unique combinations (i.e. I want Qlikview to return the value 2 because row 1 and 2 have the same combination and row 3 has a different combination. Therefore, there are 2 unique combinations in this dataset)

       

      Field 1Field 2Field 3
      ABC
      BAC
      AC

       

      Thanks

        • Re: Count unique
          Jakub Szurogajło

          Hello,

           

          I have created some code for you, which will do the trick.

          Attaching qvw file & script separately if you only have QV personal.

           

          Final result:

          Screen Shot 08-01-16 at 09.00 PM.PNG

          BR,

          Kuba

          • Re: Count unique
            Sunny Talwar

            Another possibility:

             

            Script:

            Table:

            CrossTable (Field, Value)

            LOAD RowNo() as Key,

              *

            INLINE [

                Field 1, Field 2, Field 3

                A, B, C

                B, A, C

                A, C

            ];

             

            Expression:

            =Count(DISTINCT Aggr(Concat(DISTINCT Value), Key))

              • Re: Count unique
                Sunny Talwar

                Another slightly more complicated, but can be useful if CrossTable on the whole thing is a concern can be this:

                 

                Table:

                LOAD RowNo() as Key,

                  *,

                  RangeMinString([Field 1], [Field 2], [Field 3]) &

                  RangeMinString(RangeMaxString([Field 1], [Field 2]), RangeMaxString([Field 3], [Field 2]), RangeMaxString([Field 1], [Field 3])) &

                  RangeMaxString([Field 1], [Field 2], [Field 3]) as ConcatField

                INLINE [

                    Field 1, Field 2, Field 3

                    A, B, C

                    B, A, C

                    A, C

                ];

                 

                Expression:

                =Count(DISTINCT ConcatField)


                Capture.PNG

              • Re: Count unique
                Andrew Walker

                Hi Britney,

                My script is pretty much the same as Sunny's

                Data:

                LOAD

                RecNo() as RecordID,

                *;

                LOAD [Field 1],

                     [Field 2],

                     [Field 3]

                FROM

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

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

                 

                 

                CrossData:

                CrossTable(Field,Value) LOAD * Resident Data;

                 

                But I arrived at a different expression:

                =Count(DISTINCT Aggr(Concat(Value,'|',Value), RecordID))

                 

                I reckon the third parameter in the concat function is required to ensure that different permutations of the same values are sorted so that say Field1,2,3 values of A,C,B and C,A,B are both concatenated to read A|B|C. also the DISTINCT clause is in a different place.

                 

                Cheers

                 

                Andrew