topic Re: replace nulls due to Qlik's natural join in App Development

replace nulls due to Qlik's natural join

mmarchese — Fri, 09 Nov 2018 20:04:08 GMT

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

Re: replace nulls due to Qlik's natural join

swuehl — Fri, 09 Nov 2018 20:24:03 GMT

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.

Re: replace nulls due to Qlik's natural join

swuehl — Fri, 09 Nov 2018 20:26:45 GMT

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.

Re: replace nulls due to Qlik's natural join

mmarchese — Fri, 09 Nov 2018 21:14:37 GMT

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.

Re: replace nulls due to Qlik's natural join

mmarchese — Fri, 09 Nov 2018 21:46:23 GMT

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