topic Re: Count NULL values using Set-Analysis in QlikView

Count NULL values using Set-Analysis

clondono — Tue, 01 Mar 2016 15:57:45 GMT

Hi,

I'm trying to count the number of DISTINCT projects that have NULL values for a specific field. Not all projects have null values for this field and some projects might have more than one null value for the field. I'm using this count in a straight table for a Pivot Chart in NPrinting. I'm getting either an "error in set modifier expression" or an invalid result.

This expression returns the modifier expression error:

=Count(DISTINCT{$<isNull(TASK_NAME)>}PROJECT_ID)

This other expression gives me the count of ALL projects not only the count of projects with NULL values for the field:

=Count(DISTINCT{$<TASK_NAME ={0}>}PROJECT_ID)

Thanks!

Re: Count NULL values using Set-Analysis

sunny_talwar — Tue, 01 Mar 2016 17:35:40 GMT

Try this:

=Count(DISTINCT{$<TASK_NAME = {"=Len(Trim(TASK_NAME)) = 0"}>}PROJECT_ID)

Re: Count NULL values using Set-Analysis

clondono — Tue, 01 Mar 2016 22:19:36 GMT

I tried your solution but it gives me the count of all projects, not only the count of projects with NULL values for the field, the same results as this:

=Count(DISTINCT{$<TASK_NAME ={0}>}PROJECT_ID)

Re: Count NULL values using Set-Analysis

sunny_talwar — Tue, 01 Mar 2016 22:39:53 GMT

What about this:

=Count(DISTINCT{$<TASK_NAME = {"=NullCount(TASK_NAME) > 0"}>}PROJECT_ID)

Re: Count NULL values using Set-Analysis

Peter_Cammaert — Tue, 01 Mar 2016 22:45:16 GMT

Sunny, maybe this minor tweak could be useful:

=Count(DISTINCT{$<PROJECT_ID = {"=NullCount(TASK_NAME) > 0"}>}PROJECT_ID)

Re: Count NULL values using Set-Analysis

swuehl — Tue, 01 Mar 2016 23:13:48 GMT

Or maybe without set analysis

=Count(DISTINCT If(Len(Trim(TASK_NAME))=0, PROJECT_ID))

Re: Count NULL values using Set-Analysis

clondono — Tue, 01 Mar 2016 23:14:58 GMT

Thank you Sunny and Peter, both your answers pointed me in the right direction.

I created a FlagMap in my load statement and created the following expression to get the correct results:

=Count(DISTINCT{$<Flag_Map_Field = {"=NullCount(TASK_NAME)>0"}>}PROJECT_ID)

Re: Count NULL values using Set-Analysis

clondono — Tue, 01 Mar 2016 23:38:14 GMT

I forgot to include the sample of the mapping load:

Task_SeqMap:

Mapping LOAD

TASK_NAME,

FROM [..\QVD\01_Extract_QVD\MYQVD.QVD]

(qvd);

LOAD

MyFields,

...

ApplyMap('Task_SeqMap',TASK_NAME ,Null()) as Flag_Map_Field, //APPLIED MAPPING TO FIELD

...

FROM [..\QVD\01_Extract_QVD\MYQVD.QVD]

(qvd)

;

Re: Count NULL values using Set-Analysis

Anonymous — Wed, 02 Mar 2016 06:21:47 GMT

count({<column name={'0'}>}coulmn name)