How to address non existent (less than NULL) in linked tables

[oddgeir]

I need assistance in creating "something", or at least to be able to address the "nothing" when using linked tables. 

The IsNull() and other ways to address little content seems to fail once the dimension with missing content is an active part. 

Example

Table1:
Load * Inline [
Something,link
a	,	1
b	,	2
c	,	3
];

Table2:
Load * Inline [
SomethingElse,link
x	,	1
y	,	3
z	,	4
];

This will give a table like this

Something	SomethingElse
a	x
b	-
c	y
-	z

In the hunt for missing information, it is really useful to be able to address the gap itself. Therefore I "left joined" content to same table and then reiterated once more to find the missing content. But I have a feeling there should be a smarter way, I just haven't found it yet. Maybe just because it's not easy to search for how to deal with a nothing that is not null.  But hopefully you smart guys are able to give me some "best practice" for addressing holes in my linked data. 

[MarcoWedel]

maybe one possible solution:

NULLASVALUE Something, SomethingElse;
SET NullValue = 'NULL';

Table1:
LOAD * Inline [
Something,link
a	,	1
b	,	2
c	,	3
];

Join (Table1)
LOAD * Inline [
SomethingElse,link
x	,	1
y	,	3
z	,	4
];

NoConcatenate
Table2:
LOAD * Resident Table1;

DROP Table Table1;

[oddgeir]

Thank you for your suggestion. This is definitely an approach to reach the desired end result. However right now I'm investigating if this is possible while keeping the tables linked and not joined into one. 
You're still pushing to one table, although through a different method. 

I'm just curious if it is possible to keep data in their initial table instead of building a potential huge table of the combinations, only to use "nothing" in filters.   Let's say for the cause of massive tables, where only a few rows were relevant, maybe also in more complex many-to-many relations in many directions.  I don't have a specific case where this is a huge problem, but investigating alternatives to my relatively complex "one table to rule them all" approach, where addressing "nothing" is main reason it's not handled in linked tables.

[Vegar]

It is hard to search for things that does not exist. This is one take on how to do it.

• Select all Something values in your application. 
• Then in the SomethingElse field select all excluded values.
• Now you have all SomethingElse values that lacks  association with any Something value.

[oddgeir]

Thanks. I've considered this approach too.  It should produce the expected results. The reason I'm checking alternatives is to see if there's a more user friendly direct approach.  I guess end users may be a bit confused by negative filtering. Seeing with own eyes that there's either content or a defined "nothing" and then be able to directly choose the "nothing" is cognitively comforting. 

Thank you for your input. Although there's no magic bullet (yet) which solves everything it's good to know that there's not an obvious solution I've missed. 

Maybe it could be a request to Qlik, to provide an option to directly select "everything except..." which then could be * (anything defined), or even better, to make functions address also content missing due to linked tables. 

[marcus_sommer · Accepted solution]

I think what you want to reach isn't possible. Means to get the information and a selection possibility which data are missing - directly or in regard to an association - and this in a simple and easy way. Just showing the information isn't a challenge in Qlik because your table-example and coloring-feature of Qlik showed it quite obviously. I remember there was an interesting blog-posting - I think from HIC - which explained the power of grey - but unfortunately, I couldn't find it anymore, maybe it's "archives" like other older content ... That's much more as other tools could do and a real strength of Qlik's associative engine.

But to get anything selectable what doesn't exist is in general impossible - unless the missing data are created. This might be intermediate tables like it's often done in Excel that just any parts of fact- or dimension-tables are copied to another sheet and any vlookup or sumif are used to pull the data from other tables and the #NV are replaced with zero. But this is in reality an extra onion layer. The question here is - is this really sensible within a BI tool respectively a classical reporting output? It may look like a rather slight and easy to implement feature, but I assume it's the complete opposite and the fact that no tool I know has it implemented could be taken as a strong hint ... Beside this I have serious doubts to the added value of a NULLer analysis because in my experience the business is usually not able to use the data appropriate.

In regard to your case I suggest also to consider merging the tables into a single fact-table. Officially recommended is the use of a star-scheme data-model as best compromise in regard to efforts, readability, maintainability and performance. Beside the suggestion from @MarcoWedel to apply NULL variables and joining the tables you might also use mapping-approaches (more flexibility, no risks like joins have, better performance). Another method would be just to concatenate the tables - just harmonizing the fieldnames and adding an extra field 'Source' to be able to differentiate between the sources. That's very easy.

A further possibility would be to run the tables against each other with an exists() clause and creating for each missing key-value an extra record - very important is here another extra field containing the information if the record is native or populated. Of course, this needs some efforts ... Now the fact-tables could be directly linked whereby the concatenate approach has further benefits because here it's simple to connect any dimension-tables.

To conclude, there are several possibilities, but none is really simple. 

 - Marcus

[oddgeir]

Thanks for many good suggestions. Really appreciated. 

I do realize I might be asking for the impossible here, and as previously mentioned, this is just as much a check on whether I have missed something obvious as it is expectations that there is a magic bullet around the corner. I also realize that if this was simple, straightforward and useful someone would probably already have done it. 

Although my brain doesn't immediately see why a "nothing" caused by an unmatched link is more difficult than a "nothing" as part of a table (aka NULL) , I realize there may be countless reasons why things are the way they are. I have no  problems accepting that situation as long as I have checked. It would be disappointing if there actually was a solution I just hadn't found, but you kind of answer my question with "there are several possibilities, but none is really simple"