Select non-associated null values

[iacopo_panfi_BGP]

Hi,

i have a question about the selection of "non-associated" null values in Qlik Sense.

• Scenario

- Two table in the data model linked by the filed "a"

- The Table1 contains 3 records with 1 null value in the filed "c"

- The Table2 contains only 1 record for the key 001

• Script

Table1:
Load a, if(len(trim(b))=0,Null(),b) as b;
Load * Inline [
a,b
001,xxx
002, yyy
003];


Table2:
Load * Inline [
a,c
001, zzz];

• Result

• Now i want to allow the users to select the "null" values in the filed b and c
• I'm trying to use the "NullAsValue" function but it only works for the null value inside the single table and doesn't work for the "non associated" values of table b. This is the result:

How can I make the "non-associated" values in Table2 selectable? 

Thanks!

P.S.

I know that in this simple case I could join the two tables with a join and then apply the nullasvalue statement. However this is just a simple example I created to get the concept across. In the real case I'm developing I have about 20 tables with many records and it's not possible to structure a data model with a single table (using join or concatenate)

[Or]

Depending on the complexity level and specific requirements, you may be able to achieve this relatively easily by using a calculated dimension, e.g.

You could also make a second pass on your tables (Load from resident) and replace the null values with non-null values, but that has other implications that you  may not want to deal with.

[iacopo_panfi_BGP]

Thanks for you reply @Or 

Unfortunatly i'm working with an old version of Qlik Sense and the function Colaesce seem that doesn't exist

Instead i think that a second pass in the tables with resident could not resolve the issue for the table2 for the non associeted values.

[Or]

If you don't have access to Coalesce(), you can just use if(Isnull(Field),'Something',Field) which achieves the same result.

[iacopo_panfi_BGP]

Thank you very much @Or  it's a good solution but in my scenario it has a significant impact . I would have to apply the syntax in almost 800 fields with the Aggr function...and that kills the performance.

I think that the only solutions is to work via script or find some extensions that allows to select null values.

[Or]

I can't imagine a scenario where your table has 800+ dimension fields... that seems like an odd way to use Qlik (or any BI product, really).

I don't know that there's a way to do this script-side when the values are actually missing whereas opposed to a null value being loaded, which is the case for the field 'c' in your scenario. You'd have to add the missing values by concatenating the missing 'a' values into Table2 with a null-replacement value attached, I think, which would work but may impact other things in your application in an undesirable way.

As a workaround (at least in this simplistic scenario) you could lock all selections, select all values of c, and then select excluded on field a (and then clear the lock). You could apply this with button actions, I think, so the user would just have to select which field to operate on (using a variable or whatnot) and then click a button to find the related values. However, depending on the complexity of the data structure, this may not be possible. 

As far as I know, there is no way to select actual nulls in Qlik, natively or using extensions, and I don't see any other solution script-side since the issue here is the joins, not the data. Perhaps others might have ideas for this...

[marcus_sommer]

I suggest to rethink the intended data-model carefully. It's officially recommended to develop a data-model in the direction of a star-scheme. Of course it may not be trivial by many tables but this doesn't mean that's not possible. And this means the fact-table as well as dimension-tables (they could be in general also concatenated).

Such things could be often done by creating a stream-data table-structure. This means it rather simply words there is only one KPI field and one VALUE field in the fact-table beside some ID fields and maybe a DATE. By many KPI's you may (sub) categorize the KPI field as well as the ID fields respectively using also only one ID field and another which defined which ID-Type is included. We use this concept in may reports and especially the complex ones with multiple sources, dozens of KPI's, different granularity and of course also missing values which will be populated and NULL's replaced with real values where it's needed (only a few specific cases and not against the whole data-set).

IMO that's logically quite simple and everything what's possible to handle multiple fact-tables will become more difficult. Therefore why not doing simple things in an easy way instead of making it far more complicated.

Beside this Qlik doesn't store NULL in any way and therefore it's not directly accessible or selectable. Any bypass logic will have some costs ...

- Marcus 

[iacopo_panfi_BGP]

Thanks for your reply @marcus_sommer.

Your idea sounds very interesting but I don't really understand the proposed solution and how it could be adapted in my scenario. Would you happen to have any examples or documentation I could look at?
At the moment I have linked the tables through a link table that contains the keys of each table and the date field.

The main purpose of the app is to enable the users to create, with self bi functionalities, custom tables that combine fields from different tables and this is why it's important the selection also for the NULL (non associted) values.

The only way that i see at the moment is to create a "fake" records in each table for the missing keys and setup all the fields to null()...then with the NullAsValue functions make it selectable. I don't like it as a solution and it will impact the size and performance of the app, but I haven't found anything better at the moment.

[marcus_sommer]

I do understand what do you want to get but I'm not sure if it's really possible. One really big big  strengths of Qlik is the associated data-model and it will work greatly with a model like yours by connecting the data as they are. This means no linked data will produce NULL and missing keys on any side will result in differences between certain views.

In regard to the NULL there is no real challenge if you don't want to make them accessible else using the power of grey to show which data are connected to each other while moving forward and backward through the analysis. More difficult are probably missing keys which may easily lead to different results in various views if there are for examples sales which has no budget and revers.

Experienced users with a good understanding of the underlying data-model and with the specific business processes could work with it but for the probably intended normal users there are too many dependencies as that I would implement such an approach. I think it's in general critical to push so many tables with 800+ fields into a data-model to enable a self-service BI. In my experience these possibility is more a buzz-word as a added-value feature (in general and not only related to Qlik) and the users need and also require a more guided reporting.

In this regard I could only repeat my suggestion from above to consider a general change within your approach.

- Marcus