NULL values type two

[mlattemann]

Dear all,

I have problems dealing with NULL values. As I figured out by the paper "NULL and Nothing" I am dealing with "Missing values of type two" (see page 5). On page 11 there is a way described to work with those NULL values, however I cannot find the setting which is described there: "Chart Properties -> Presentation -> Populate missing cells". Does the paper refers only to QlikView and not to QlikSense? If so, how to work with these kind of NULL values in QlikSense?

So as an example I create a chart from two different tables

Table1:

ID	First date
001	First Date 1
002	First Date 2
003	First Date 3
004	First Date 4

Table2:

ID	Second date
001	Second Date 1
003	Second Date 3

When I create a Table chart out of them I got:

ID	First Date	Second Date
001	First Date 1	Second Date 1
002	First Date 2	-
003	First Date 3	Second Date 3
004	First Date 4	-

However these missing cells are not detected by the isNull function (according to the paper: missing value type two) and e.g. cannot be marked with a specific background color.

Is there a way to handle this type two NULL values?

Thanks,

Marc

[PabloTrevisan]

Look if it help's you.

[mlattemann]

Thanks Pablo,

So the background color expression directly on the column doesn't work in your example as well, because this line

if(len([Second Date])> 1,green(),'no date')

doesn't return "no date" in case of Null values (I tried blue() instead of 'no date' as well and it doesn't work either).

However I modified your code for the "Return" column to

if(Not IsNull([Second Date]),date([Second Date]),'NOK')

So the trick might be, not using the isNull function on the existing column but create a new one instead and fill it in case it's not null... And on this column also the coloring of the background works as intended.

So thanks again Pablo for your extensive help and patience

Marc

[PabloTrevisan]

Nice Marc !

Normaly IsNull works....

It solves your problem ?

[mlattemann]

Not sure if I'm correct (still need to learn). but the "Background Color Expression" can only work properly if the field in the table really exists. If you have in the "Field Expression" an isNull function and fill the field with blank (or better: create the field in the table), then also the isNull function in the "Background Color Expression" can color the field appropriate. But if the field in the table is greyish and with the dash it doesn't exist and cannot be colorized.

That's my impression I got by solving this topic here...

So to your last question: yes, it's solved but not sure which reply I should mark as answer?

Thanks again,

Marc

[PabloTrevisan]

I tried to use NullAsValue but with the same result.

Normaly IsNull works....