Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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).
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.
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
This works fine.
Thanks!!
This is a good idea. However, it would fail if the UniqueFieldName has some null values where the DATE has values.
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)