topic Re: Selecting NULL in chart in App Development

Selecting NULL in chart

wayne-a — Wed, 13 Sep 2017 18:56:24 GMT

Hi, I have chart built from two .qvd files. It results in one of the bars holding the NULL values which shows great but it can't be selected to filter on in order to see the detail and that may be the most interesting information that the user would want to see.

I have to .qvd files of information.

File 1 - Company

Tester_ID

Company

File 2 - Tests

Order_Number

TestID

Tester_ID

In the data manager the association for Tester_ID was made so that the two tables are joined. In the bar chart I'm showing the testing broken out by company. There are some tests that have a Tester_ID not in the company file so these result in the null values. I've tried a few things in the load script (NullAsValue, a resident load) to give the nulls a value but none of them work. The best I could come up with was to load the company file first and then have a statement like this to set an additional field.

If(Exists(Tester_ID),'Yes','No') as [Has_Company]

I can then filter on this to get the rows that don't have a company but I really want the value in the bar chart to be selected. Thanks in advance for any help or insight.

Re: Selecting NULL in chart

sunny_talwar — Wed, 13 Sep 2017 19:10:48 GMT

All these things you have tried thus far will have to be done after the two tables have joined? Have you tried doing that?

Re: Selecting NULL in chart

sunny_talwar — Wed, 13 Sep 2017 19:12:14 GMT

Oh it seems that you are not explicitly joining the two tables, is that correct?

Re: Selecting NULL in chart

swuehl — Wed, 13 Sep 2017 19:15:11 GMT

Maybe like this

Company:

LOAD

Tester_ID,

Company

FROM ...;

Concatenate (Company)

LOAD * INLINE [

Tester_ID, Company

Unknown, Unknown

];

Tests:

LOAD

Order_Number,

TestID,

Tester_ID as Original_Tester_ID,

If(Exists(Tester_ID),Tester_ID,'Unknown') as Tester_ID,

...

FROM ...;

Re: Selecting NULL in chart

wayne-a — Wed, 13 Sep 2017 19:45:09 GMT

Hi Sunny, thank you for the reply. Yes, I don't have an explicit join statement in the load script. I'm letting Qlik Sense make the connections and verified them in the data manager.

Re: Selecting NULL in chart

wayne-a — Wed, 13 Sep 2017 20:09:05 GMT

Hi Stefan, thank you for the reply. Yes, very good! The only downside is that with the statements in the load it eliminates the optimized load of the .qvd file but that may be the price that has to be paid in order to get the functionality. Thanks for the help.

Re: Selecting NULL in chart

swuehl — Wed, 13 Sep 2017 20:36:43 GMT

If you are only interested to select the testers that aren't part of a company, select all companies, then select excluded from the tester filter pane.