Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mlattemann
Contributor II
Contributor II

NULL values type two

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:

IDFirst date

001

First Date 1
002First Date 2
003First Date 3
004

First Date 4

Table2:

IDSecond date
001Second Date 1
003Second Date 3

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

IDFirst DateSecond Date
001First Date 1Second Date 1
002First Date 2-
003First Date 3Second Date 3
004First 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

14 Replies
PabloTrevisan
Partner - Creator II
Partner - Creator II

Hi Marc,

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

Screenshot_1.jpg

I hope it help's

mlattemann
Contributor II
Contributor II
Author

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:

IDFirst DateSecond Date
001First Date 1Second Date 1
003First Date 3Second 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

PabloTrevisan
Partner - Creator II
Partner - Creator II

Hi Marc.

Now I get it...

Here's what I did with what I did.

exdates.jpg

This is what you need?

mlattemann
Contributor II
Contributor II
Author

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

PabloTrevisan
Partner - Creator II
Partner - Creator II

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,

mlattemann
Contributor II
Contributor II
Author

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

PabloTrevisan
Partner - Creator II
Partner - Creator II

Hi Marc, no problem !

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

I will ajust for you and get prints of the screen

mlattemann
Contributor II
Contributor II
Author

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

PabloTrevisan
Partner - Creator II
Partner - Creator II

Marc,

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