Count unique

[#1]

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 1	Field 2	Field 3
A	B	C
B	A	C
A	C

Thanks

[#2]

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:

BR,

Kuba

[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))

[effinty2112]

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

[sunny_talwar]

effinty2112‌ -

I don't think the third parameter is necessary because concat() function usually sort in ascending sort order of the field. So even without Value provided, both the first the second row will be ABC, ABC.

[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)