topic Re: NULL values type two in App Development

NULL values type two

mlattemann — Fri, 27 Jul 2018 09:08:36 GMT

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

Re: NULL values type two

PabloTrevisan — Fri, 27 Jul 2018 15:11:41 GMT

Hi Marc,

try to in the chart properties select the dimension 'Second Date' and mark this option ' Suppress When Value Is Null'

I hope it help's

Re: NULL values type two

mlattemann — Mon, 06 Aug 2018 08:59:47 GMT

Hi Pablo,

thanks for your reply (sorry I was one week on vacation, therefore the delay...)

This is not what I'm looking for. The resulting table is looking like below, when I'm choosing this option:

ID	First Date	Second Date
001	First Date 1	Second Date 1
003	First Date 3	Second Date 3

I don't want to suppress those lines, but rather "work" with those empty fields. E.g. if date 2 is more than 30 days after date 1 then mark it red if it's still empty to indicate that action is needed.

According to my cited paper these "missing" dates are not evaluated by qliksense and I cannot find this mentioned option "Populate missing cells".

Kind Regards,

Marc

Re: NULL values type two

PabloTrevisan — Tue, 07 Aug 2018 13:32:18 GMT

Hi Marc.

Now I get it...

Here's what I did with what I did.

This is what you need?

Re: NULL values type two

mlattemann — Tue, 07 Aug 2018 14:02:13 GMT

Hi Pablo

This is exactly what I'm looking for. But the isNull function does not detect the null fields (gey fields with dash).

So my "Background color expression" simply looks like:

=if(isNull([Second Date]),yellow())

BTW: the oposite "Not isNull" works (and also coloring some differences between first and second date), so it seems to me the color coding function itself is working - but not with the null values.

Thanks a lot,

Marc

Re: NULL values type two

PabloTrevisan — Tue, 07 Aug 2018 20:45:12 GMT

Great Job Marc !!

Nice that it worked for you...

Please, don't forget to check as the correct answer if it solved your problem.

Bey,

Re: NULL values type two

mlattemann — Wed, 08 Aug 2018 08:26:06 GMT

Dear Pablo,

sorry I was unclear: I want to achive what you have in your example but I cannot... When I open your qvw-file in QlikSense-Desktop I see your scripts, but the apps are not there. So I still don't know how you managed your color coding

I'm still a beginner, so maybe I did something wrong when importing the qvw-file to QlikSense-Desktop...

Thanks so much for your help,

Marc

Re: NULL values type two

PabloTrevisan — Wed, 08 Aug 2018 11:39:03 GMT

Hi Marc, no problem !

Please, send me your .qvw with the data model.

I will ajust for you and get prints of the screen

Re: NULL values type two

mlattemann — Wed, 08 Aug 2018 13:01:51 GMT

I took your template in my local desktop installation and created an app with a table with my code from above and uploaded as qvf (I don't know how to transform to qvw).

Still the NULL values are not color coded - so the issue is the same as my original data on the server.

I don't know how to get my original data from the server as qvf or qvw file, but your data reflect my situation quite good and simple.

Marc

Re: NULL values type two

PabloTrevisan — Wed, 08 Aug 2018 17:07:13 GMT

Marc,

my mistake, I did it on qlikview and not on qlisense, I'm going to fix it now!

Re: NULL values type two

PabloTrevisan — Wed, 08 Aug 2018 17:44:04 GMT

Look if it help's you.

Re: NULL values type two

mlattemann — Thu, 09 Aug 2018 07:28:48 GMT

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

Re: NULL values type two

PabloTrevisan — Thu, 09 Aug 2018 19:20:50 GMT

Nice Marc !

Normaly IsNull works....

It solves your problem ?

Re: NULL values type two

mlattemann — Fri, 10 Aug 2018 08:57:30 GMT

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

Re: NULL values type two

PabloTrevisan — Fri, 10 Aug 2018 13:03:55 GMT

I tried to use NullAsValue but with the same result.

Normaly IsNull works....