topic Re: Identifying & Returning Nulls in QlikView

Identifying & Returning Nulls

Mon, 31 Oct 2011 14:47:26 GMT

I have a chart and I would like to display all records with a null value in my expression. Currently the expression returns records with a value of <0, but I would also like to see nulls. I have played around with if(IsNull...) and LEN(TRIM) have not gotten it to work yet. Here is my current expresion, and I'm attaching a sample. I want to list all store numbers with <= 0 cases shipped. Does anyone know if it is possible to identify nulls in this way?

=concat({$<STORE#=>}distinct if(aggr(sum(QTY_SHIP),STORE#,[Product No])<0.001,STORE#),', ')

Re: Identifying & Returning Nulls

Mon, 31 Oct 2011 15:05:05 GMT

Having looked at the display options for the chart in your example, the data you want to show as 0 is not a null value, but a missing value (chart properties -> presentation and change the null and missing symbol options).

So you could just set the missing symbol to be 0? Otherwise you might be able to force the missing data to be nulls with a value of 0 in the load script.

Message was edited by: rbiscoeodi

Re: Identifying & Returning Nulls

Mon, 31 Oct 2011 15:24:13 GMT

Thanks for the suggestion. Changing the the null & missing symbols did not change the result. I added an image that shows the result I am looking for in my sample. I can't change the load script, so my solution will have to be in the object itself.

Re: Identifying & Returning Nulls

swuehl — Fri, 04 Nov 2011 10:34:10 GMT

Hi Nicole,

maybe like attached? Doing this in the frontend is quite a heavy requirement, since as stated above, it's not a NULL problem, it's a missing problem.

I could build a string with all values for Store#, though, and a string with the 'non voids', then I needed to subtract both strings, the resulting code looks a bit strange, cause you need to iterate within an expression over substrings, I used valueloop for that (I really begin to like this function).

Maybe I missed a much easier solution, I had fun doing the string subtraction anyway.

Hope this helps somehow,

Stefan

Re: Identifying & Returning Nulls

jedgson — Fri, 04 Nov 2011 11:05:24 GMT

Hi Nicole,

I had a similar issue which I resolved and posted about here:

http://community.qlik.com/message/8618#8618

Jay