topic Re: Hiding when value is null in a List Object in QlikView

Hiding when value is null in a List Object

Mon, 19 Oct 2015 16:50:49 GMT

Hello everyone,

I would like to create a List in my project, to have a filter of "Red" and "Green" values, but my list is also displaying the null value, as you can see below:

Is it possible to hide the null value in this list object?

Also, i am selecting "hide when value is null" from a pie chart properties (Dimensions tab), but the nulls are still been displayed, as you can see below:

That pie chart is supplied from many bases (xlsx), and i am nout sure if the problem is related to this.

Thanks

Re: Hiding when value is null in a List Object

Oleg_Troyansky — Mon, 19 Oct 2015 17:11:04 GMT

Hi Rodrigo,

nulls are not displayed in List Boxes. Considering that "Suppress null values" didn't work in a chart either, I suspect that this is not a null value, but rather an empty string (or a space). You should look closer into your data load script and find how this value could be generated there.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Hiding when value is null in a List Object

Mon, 19 Oct 2015 17:20:58 GMT

Hi Oleg,

Thanks for your tip.

I looked into my data, and saw there is a formule in all cells of that column [Status Mês]:

=SE(S6 = "";" "; SE(O6>=T6;"Verde";"Vermelho"))

So, in this case, how can QlikView understand that blank cells should be hidden.

Thanks

Re: Hiding when value is null in a List Object

Oleg_Troyansky — Mon, 19 Oct 2015 17:27:02 GMT

I'm not sure what's SE - is that your own function that returns a certain value? Anyway, it looks like it might return a space or an empty string in some situations, and that is what's displayed in the list box.

Re: Hiding when value is null in a List Object

beck_bakytbek — Mon, 19 Oct 2015 17:30:10 GMT

Hi Rodrigo,

you can use the following option: Dimension>Used-Dimension>Supress When Value is Null

hope that helps

Beck

Re: Hiding when value is null in a List Object

Mon, 19 Oct 2015 17:38:31 GMT

SE is a IF function... My spreasheet is in portuguese. Sorry to didn´t clarify.

So, my business user create this function in the database:

=IF(S6 = "";" "; IF(O6>=T6;"Green";"Red"))

Re: Hiding when value is null in a List Object

Oleg_Troyansky — Mon, 19 Oct 2015 18:20:32 GMT

Rodrigo,

got it... so, here is the source of the "space" value - when S6 is empty, the formula returns a single space as the value, and only when S6 is not empty, then the formula returns either Green or Red.

So, in QlikView, if you'd like to replace the single space with a NULL (in order to easily exclude it), you can have a similar IF statement in your load script:

IF(Color = ' ', NULL(), Color) as Color

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Hiding when value is null in a List Object

Oleg_Troyansky — Mon, 19 Oct 2015 19:07:36 GMT

Hi Rodrigo,

I used the name Color as an example. Please replace it with the actual field name that you used in the list box that you showed initially.

cheers,

Oleg

Re: Hiding when value is null in a List Object

Mon, 19 Oct 2015 19:16:51 GMT

Thanks a lot Oleg, it solved the problem.

Cheers