Dealing with Null Values after tables join using a key.

[Anonymous]

Hello,

I am having an issue with some null values. The issue arises when I want to select the null values on a graph.

3  tables are connected via 2 keys. Table A has Key1 which joins to Key1 on Table b and Table c joins to table b via key 2. Table A consists of a number of cases and their attributes, while table b has the nature of these cases and table c has the number case numbers and people working on the cases. Here is where the problem lies. Some attributes have been left out on table A, if I create a graph of those attributes, the null values appear but I cannot select these blank attributes to show which case numbers they are referring to.

I have attached my example. I would like to do this with out creating a large join of the tables if possible.

The final result I would like is to be able to select the 'waiting' category to show all the case's that are still with outstanding data.

This will reflect across the other sheets I add to the model.

Thank you!

[sunny_talwar]

In order to make them select-able, I think you would need to join these tables and use your current calculated dimension in the script to replace nulls with 'waiting'. AFAIK you won't be able to select null on the front end of the application

[swuehl]

Wouldn't it be enough to replace your first table LOAD with

LOAD Key1,

    If( len(trim(Attribute)), Attribute, 'waiting') as Attribute

FROM

(ooxml, embedded labels, table is Sheet1);

[swuehl]

Another option without any change in the model:

Select all possible attributes, e.g. by selecting all 4 quarters of your pie chart or the legend (which will remove 'waiting').

Then right click on the Case_Number in your table box (or use a list box for Case_Number or a dimension) and select menu entry 'Select excluded'.

[Anonymous]

So I made a small error in the example.

In my data I do not have a key that connects table 1 and table 3. So I do not think this would work as there is no field in that table? Or will it create the field for the missing key values?

[swuehl]

Not sure what you are talking about, your sample also does not have a key between table 1 and 3, those are connected via table 2.

But there is an attribute field in table 1 with NULLs. You can modify this field to replace NULL with values you can select (like 'waiting' or 'N/A' or something else). You can also create an additional field, if you don't want to replace NULL.

Or use the other approach using the Qlik associative logic (i,e, select all possible values in attribute field, then select 'excluded' in the other field you are interested in).

You can also use this logic in set analysis or using button with multiple actions.

If possible, create a field in your model. It makes your life just easier.