Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gkluca82
New Contributor III

Distinct count on a field

Hello everyone,

i've got a problem.

I've a key field named DATE. But i want to distinct count the values of DATE only in a particular table.

if i simply set: count(distinct(DATE)) i'll get the sum of the distinct date over the different tables with the field DATE.

How can i achieve this?

tnx in advance.

Luca

1 Solution

Accepted Solutions

Re: Distinct count on a field

May be use a field which is only available in that table within your set analysis like this:

Count(DISTINCT {<UniqueFieldName = {'*'}>} DATE)

UniqueFieldName is a field which is only available in that table from which you want to do the UniqueCount

6 Replies
MVP
MVP

Re: Distinct count on a field

For that, you have to create an additional date field in the same table. May be like:

Load

          Date,

          Date as DateTable1

;

And then you can use Count(Distinct DateTable1).

Re: Distinct count on a field

If the field has the same name, then there is actually only one instance of that field in QlikView.

The tables are there to help users identify the relationships between the data.

As far as QlikView is concerned the tables do not exist - you only ever reference a field by its name not by its table.

If you want to count the occurrences of a value in a specific table create a unique field for that table.

Re: Distinct count on a field

May be use a field which is only available in that table within your set analysis like this:

Count(DISTINCT {<UniqueFieldName = {'*'}>} DATE)

UniqueFieldName is a field which is only available in that table from which you want to do the UniqueCount

gkluca82
New Contributor III

Re: Distinct count on a field

This works fine.

Thanks!!

MVP
MVP

Re: Distinct count on a field

This is a good idea. However, it would fail if the UniqueFieldName has some null values where the DATE has values.

Re: Distinct count on a field

That is true, may be we can create a flag in the script for a the table:

LOAD *,

          1 as Flag_Table1

FROM Source;

and then this: Count(DISTINCT {<Flag_Table1= {'*'}>} DATE)

Community Browser