topic Re: COUNT DISTINCT across several fields in QlikView

COUNT DISTINCT across several fields

Mon, 10 Mar 2014 17:44:52 GMT

I know how to count distinct values for one field. But how do I count distinct combinations of fields for each row of data.

Is this anywhere close to be correct?

COUNT (DISTINCT Activities.Subject & Activity.CalendarDate & Activities.Type & Activities.Description)

I tried this and it seems to work, however for a couple of months this produces more results than for the individually counted rows! I'm obviously doing something wrong as this should be impossible.

Re: COUNT DISTINCT across several fields

Mon, 10 Mar 2014 18:29:31 GMT

The way I would approach this:

Get all the fields required into one table on the backend and then use autonumberhash to create a key out of those fields.

Autonumberhash128(Activities.Subject,Activity.CalendarDate,Activities.Type,Activities.Description) as KeyField

Then on the front end you can use count(Distinct KeyField)

Thanks

Re: COUNT DISTINCT across several fields

maxgro — Mon, 10 Mar 2014 21:16:01 GMT

I prefer to add add a separator between fields

COUNT (DISTINCT Activities.Subject & '-' & Activity.CalendarDate & '-' & Activities.Type & '-' & Activities.Description)

As Ayaj suggests is better to do at the backend, just another way is

Activities.Subject & '-' & Activity.CalendarDate & '-' & Activities.Type & '-' & Activities.Description as KeyField

In my experience there is some (sometimes big) improvement in chart time calculation when you change

from

COUNT (DISTINCT Activities.Subject & '-' & Activity.CalendarDate & '-' & Activities.Type & '-' & Activities.Description)

count(Distinct KeyField)

Re: COUNT DISTINCT across several fields

Clever_Anjos — Tue, 11 Mar 2014 01:57:13 GMT

Are your fields from same table?

If yes, please use Ajay or Massimo suggestions.

If they´re coming from more than one table, you have to use some

LOAD distinct

Activities.Subject

Resident T1

....

to calculate what you want

Re: COUNT DISTINCT across several fields

Tue, 11 Mar 2014 09:15:59 GMT

Thanks Massimo, I will try you suggestions of using the separator. As this is simpler for me

How can I ensure that rows with no data are ignored in the count? I have a row for each date, regardless if there is an activity on it, but the blank rows are being counted

Re: COUNT DISTINCT across several fields

Tue, 11 Mar 2014 09:35:42 GMT

try this

load

Activities.Subject & Activity.CalendarDate & Activities.Type & Activities.Description as key

from table;

then try this

count(Distinct key)