Counting null values using Set Analysis

[Anonymous]

Hello All,

I have a query regarding counting null values using Set Analysis.

Change Order Number	Check
CR1234	-
CR4567	-
CR7891	-
CR2345	1
CR3456	0
CR5643	-
CR1234	-
CR1234	-

The data being used is as mentioned above. I need to do a distinct count of all the change orders having a check value of '-' ie. null or '1'. I have to exclude all the change orders having a check value '0'.

Suggestions are much appreciated.

Thanks and regards,

Erwin Palani

[Kushal_Chawda]

try this creating flag in script

LOAD *,

         if(len(trim(Check))=0 or isnull(Check),1,0) as CheckFlag

FROM table;

Now in set analysis

=Count(DISTINCT {<CheckFlag={1}>}[Change Order])

[antoniotiman]

Hi,

may be

=Count({<[Number Check]-={0}>} DISTINCT [Change Order])   -> 5

Regards,

Antonio

[anjali0108]

Yes ,the above script will work for you,

Also,

If you will get values like shown above only,

then you can go with,

=Count(DISTINCT {<Check-={0}>}[Change Order]) as well.

[Anonymous]

Hi Anjali,

Thanks for your response. But this counts only the Change Order's with check value '1', it doesn't count the null values.

[Marcellino_Groothof]

Hi,

Try this:

Greetings,

Marcellino

[tamilarasu]

Hi Joshua,

Try,

=Count({<Check={"=Len(Trim(Check))=0"} > + <Check={"1"}>}[Change Order Number])

[sunny_talwar]

I don't think that the first part of the set analysis will work... but with a slight change it could work

=Count(DISTINCT {<[Change Order Number]={"=Len(Trim(Check))=0 or Check = 1"}>}[Change Order Number])

I added DISTINCT and Check = 1, but these may still work your way (without DISTINCT and <Check = {1}>), but thought of changing them to streamline the expression.