How to count null values

[rkpatelqlikview]

  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.

[suryaa30]

Hi,

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

Do you want the count across each row or column?

[rkpatelqlikview]

Hi Surya,

Thanks for your reply. Please find the attached.

[vineetadahiya12]

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.

[rkpatelqlikview]

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.

[pradosh_thakur]

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

[pradosh_thakur · Accepted solution]

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

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,0,1)

[rkpatelqlikview]

Many Thanks Pradosh,

I will check with this and let you know.

[sergeyay]

Hi,

Perhaps, this article about Nulls will help

NULL handling in QlikView

Sergei

[suryaa30]

Replace Nulls with Blanks in Script,

Concatenate 3 Fields as Flag Field

Use Flag field to Check for FAIL