Count NULL values using Set-Analysis

[clondono]

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!

[sunny_talwar]

Try this:

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

[clondono]

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)

[sunny_talwar]

What about this:

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

[Peter_Cammaert]

Sunny, maybe this minor tweak could be useful:

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

[swuehl]

Or maybe without set analysis

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

[clondono · Accepted solution]

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)

[clondono]

I forgot to include the sample of the mapping load:

Task_SeqMap:

Mapping LOAD

  TASK_NAME,

  0

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)

;  

[Anonymous]

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