topic Counting NULL in a pivot chart in QlikView

Counting NULL in a pivot chart

Sun, 07 Nov 2010 22:19:34 GMT

Hello

This question appears on other threads but none of the proposed solutions work (for example, see thread: http://community.qlik.com/forums/t/24728.aspx)

All I want to do is count the number of NULL values from a field in a pivot column. Intuitively, this is what I would do:

COUNT( { $ < Transaction = {} > } ID)

This does not work. The stats box doesn't show any NULLs but another thread suggests that stats boxes are generally not to be trusted (especially with NULLs in version 9).

Other threads suggest the following. None of these work for me.

Count({$-<Transaction={}>} ID)
Count({$<Transaction= {$(=null())}>} ID)
Count({$<isnull(Transaction)= {'-1'}>} ID)
COUNT({$-<Transaction = -{}>} ID)

My workaround is to create a separate column of data on load in the script, i.e. IF(ISNULL(TransactionID), 0, 1) AS TransactionIDIsNull and then count the 1s and 0s.

This works but I was hoping for a tidier solution.

Thanks in advance
James

Counting NULL in a pivot chart

Anonymous — Mon, 08 Nov 2010 00:10:49 GMT

Rather question than an answer:
Why use set analysis? Did you try this:
count(if(isnull(Transaction),ID))

Counting NULL in a pivot chart

Mon, 08 Nov 2010 00:18:16 GMT

Thanks for your quick reply. That does indeed work.

However, I simplified the problem in my post. My actual expression is of the form:

count ( { $ < Region = {}, var1 = {val1}, var2 = {val2} > } )

So I have other set analyses going on. I was after a set analysis-related answer, if at all possible.

With thanks
James

Counting NULL in a pivot chart

Anonymous — Mon, 08 Nov 2010 01:09:46 GMT

James,
in this case, I'd be looking for a solition in this direction, combining the above expression with set analysis:
count({$<other set analysis conditions>} if(isnull(Transaction),ID))

Counting NULL in a pivot chart

Mon, 08 Nov 2010 01:19:30 GMT

That worked. Fantastic. Thanks very much indeed !!!