Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
wayneantinore-a
Not applicable

Selecting NULL in chart

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.

NullChart.JPG

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Selecting NULL in chart

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 ...;

6 Replies
sunny_talwar
Not applicable

Re: Selecting NULL in chart

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
Not applicable

Re: Selecting NULL in chart

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

swuehl
Not applicable

Re: Selecting NULL in chart

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 ...;

wayneantinore-a
Not applicable

Re: Selecting NULL in chart

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.

wayneantinore-a
Not applicable

Re: Selecting NULL in chart

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
Not applicable

Re: Selecting NULL in chart

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.