Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Counting null values using Set Analysis

Hello All,

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

 

Change Order Number Check
CR1234-
CR4567-
CR7891-
CR23451
CR34560
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

7 Replies
MVP
MVP

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])

Master III
Master III

Hi,

may be

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

Regards,

Antonio

Partner
Partner

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.

Contributor III
Contributor III

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.

Contributor III
Contributor III

Hi,

Try this:

Greetings,

Marcellino

Hi Joshua,

Try,

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

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.