topic Re: Count unique in QlikView

Count unique

Thu, 28 Jul 2016 01:37:57 GMT

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

Re: Count unique

Mon, 01 Aug 2016 19:01:31 GMT

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

Re: Count unique

sunny_talwar — Mon, 01 Aug 2016 19:10:32 GMT

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

effinty2112 — Mon, 01 Aug 2016 20:11:09 GMT

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

Re: Count unique

sunny_talwar — Mon, 01 Aug 2016 20:13:53 GMT

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.

Re: Count unique

sunny_talwar — Mon, 01 Aug 2016 20:22:47 GMT

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)