topic Count if expression in QlikView

Count if expression

Tue, 04 Sep 2012 10:52:28 GMT

Hi,

I'm new to Qlikview and have come across a blocker that I've not been able to get around.

I want to carry out a variable count of one column and based on that outcome carry out a Count DISTINCT of another column.

To put into context;

MFPN Unique ID

1 9

2 NULL

3 8

1 NULL

1 7

2 6

I want to look at Unique ID and where the value doesn't = NULL I want to carry out a Distinct Count of MFPN

I have tried

Count (DISTINCT MFPN (if ([Unique ID], (not 'NULL'), ([Unique ID])))

I know its wrong but its as close as I can get!!

Any help will be apprecited.

Thanks,

Phil

Re: Count if expression

rajni_batra — Tue, 04 Sep 2012 10:53:54 GMT

Try

Count (DISTINCT MFPN (if ([Unique ID]<> 'NULL'), ([Unique ID])))

Re: Count if expression

Tue, 04 Sep 2012 10:59:04 GMT

Thanks Rajni, however, that throws back an error with the use of the 'if' expression.

Any thoughts?

Re: Count if expression

rajni_batra — Tue, 04 Sep 2012 11:10:48 GMT

can u exactly show me what result r u expecting exactly from this kind of expression

Re: Count if expression

Tue, 04 Sep 2012 11:13:46 GMT

In the context of the example I gave it should bring back a count of 4 as 2 of the MFPN's have NULL in there corresponding Unique ID field and I want to exclude the NULL values from the DISTINCT Count.

Does that help?

Re: Count if expression

rajni_batra — Tue, 04 Sep 2012 11:20:17 GMT

=count(if([Unique ID]<>'NULL',[Unique ID]))

so i think it would be good enough

Re: Count if expression

whiteline — Tue, 04 Sep 2012 12:58:40 GMT

=count({$<[Unique ID]-={"NULL"}>} [Unique ID])

Re: Count if expression

Tue, 04 Sep 2012 13:16:39 GMT

Thanks Whiteline. Worked perfectly