topic Re: How to count null values in QlikView

How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 07:05:55 GMT

Hi,

Please find the attached excel source file. Data is coming in three stages, I need to count the number of 'PASS' and number of 'FAIL' considering only one stage.

1) Here if the Stage1 only having entry and there is no data in another 2 stages at that time we shold consider Stage1.like that for all.

2) If any stage is having "FAIL" We should consider as "FAIL".

Like this we need to count. How to count these Pass/Fail from 3 stages following by above condition.

Thanks in advance.

Re: How to count null values

suryaa30 — Mon, 26 Feb 2018 07:39:39 GMT

Hi,

Please fill the output manually that you want in the excel and share

Do you want the count across each row or column?

Re: How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 07:47:15 GMT

Hi Surya,

Thanks for your reply. Please find the attached.

Re: How to count null values

vineetadahiya12 — Mon, 26 Feb 2018 08:10:20 GMT

Hi Kumar,

Try this:

To show the Result use formula in a straight table:

If( "[Stage1]Truck Compliance" = 'FAIL' OR "[Stage2]Truck Compliance"='FAIL' OR "[Stage3]Truck Compliance"='FAIL', 'FAIL' ,'PASS')

To display the count of PASS/ FAIL , you can use:

Pass: =Count( {$<Result={'PASS'}>} Result)

Fail: ==Count( {$<Result={'FAIL'}>} Result)

Let me know if this helps.

Re: How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 08:54:21 GMT

Thanks for your reply Vineetha,

According to this condition If there is no data for Stage2 and stage3,it should be consider stage1 is the result. I think this condition is not satisfied. and Count of Pass/Fail also will give the all the pass / fail. It might not be follows the condition. Its counting all the Pass/Fail.

Re: How to count null values

pradosh_thakur — Mon, 26 Feb 2018 09:04:55 GMT

if(alt(pick(match("[Stage1]Truck Compliance",'FAIL','PASS'),1,0),0)+alt(pick(match("[Stage2]Truck Compliance",'FAIL','PASS'),1,0),0)+alt(pick(match("[Stage3]Truck Compliance",'FAIL','PASS'),1,0),0) >0,'FAIL','PASS')

Re: How to count null values

pradosh_thakur — Mon, 26 Feb 2018 09:07:00 GMT

TO COUNT FAIL

SUM(if(alt(pick(match("[Stage1]Truck Compliance",'FAIL','PASS'),1,0),0)+alt(pick(match("[Stage2]Truck Compliance",'FAIL','PASS'),1,0),0)+alt(pick(match("[Stage3]Truck Compliance",'FAIL','PASS'),1,0),0) >0,1,0)

TO COUNT PASS

Re: How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 09:23:30 GMT

Many Thanks Pradosh,

I will check with this and let you know.

Re: How to count null values

sergeyay — Mon, 26 Feb 2018 09:25:29 GMT

Hi,

Perhaps, this article about Nulls will help

NULL handling in QlikView

Sergei

Re: How to count null values

suryaa30 — Mon, 26 Feb 2018 09:26:01 GMT

Replace Nulls with Blanks in Script,

Concatenate 3 Fields as Flag Field

Use Flag field to Check for FAIL

Re: How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 11:52:00 GMT

Thank you so much Pradosh,

Its correct one

Re: How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 11:52:25 GMT

Thank you for your support Sergu,

Re: How to count null values

rkpatelqlikview — Mon, 26 Feb 2018 12:01:36 GMT

Many Thanks Surya. Perfect.

Re: How to count null values

pradosh_thakur — Mon, 26 Feb 2018 12:44:17 GMT

please close the thread if you got the answer by marking the appropriate answer correct.