Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Count unique

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

5 Replies
Not applicable

Re: Count unique

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

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
Honored Contributor

Re: Count unique

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

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

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

Community Browser