Selecting NULL in chart

[wayne-a]

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

<other test detail>

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.

[sunny_talwar]

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

[sunny_talwar]

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

[swuehl · Accepted solution]

Maybe like this

Company:

LOAD

Tester_ID,

Company

FROM ...;

Concatenate (Company)

LOAD * INLINE [

Tester_ID, Company

Unknown, Unknown

];

Tests:

LOAD

Order_Number,

TestID,

<other test detail>

Tester_ID as Original_Tester_ID,

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

...

FROM ...;

[wayne-a]

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.

[wayne-a]

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.

[swuehl]

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.