Qlik Community

Qlik Sense App Development

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

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

replace nulls due to Qlik's natural join

Simple hypothetical example: I load several tables.  The tables have no fields in common except for one column named pk, which each table has.  I don't join them explicitly in the script, so Qlik does an outer join on column pk during load.  Unless every table has the exact same set of pk's, there will be nulls since that's how outer joins work.

I can create a dimension for each field whose nulls I want to be replaced with values, but that adds clutter and seems strange to me, because why shouldn't I be able to remove these nulls once at load time instead of continuously at dashboard-use-time?

Is there any way to replace these nulls with values at load time?

I've tried things like:

NullAsValue *;

Set NullValue = 'anythingButANullPlease';

and

Set NullDisplay = 'whyHaveYouForsakenMeQlik';

and

loading the tables and then reloading them with Resident and replacing nulls at that point

but none of these techniques do anything.  Unless I'm botching the syntax...

4 Replies
Highlighted
MVP
MVP

I am not sure I understand what you are doing in your script. If you don't JOIN the tables explicitely using a JOIN LOAD prefix, I think the tables would just be linked by common field names.

So there will be no records with NULL key introduced unless the NULL comes in during your input table LOAD.

Highlighted
MVP
MVP

I assume what you might talking about is the indicator for missing association when using dimensions in e.g. a table chart made of fields from different tables, i.e. the NULL created when performing the UI run time JOIN.

Highlighted
Creator II
Creator II

Yes, if I show the fields in a table, there are nulls.  Is there any way to combat that at load time?

The only thing I can think of is to explicitly join every table in the load script so that I can try using some of the techniques I mentioned.  But that seems lame because 1) I thought not having to join things explicitely was Qlik's bread and butter and 2) then my data model will just be 1 big table instead of a meaningful diagram of many tables with relationships drawn in.

Highlighted
Creator II
Creator II

If you don't JOIN the tables explicitely using a JOIN LOAD prefix, I think the tables would just be linked by common field names.

Yeah, that's what I meant by "natural join."

So there will be no records with NULL key introduced unless the NULL comes in during your input table LOAD.

Maybe I am misunderstanding Qlik.  I thought "associative data model" was just marketing speak for "natural full outer join" (i.e. a join that results in a table containing nulls even when none of the tables being joined contained nulls to begin with).