Removing Nulls (from after Joins)

[ruanhaese]

Hi everyone

I'm dealing with some data where the data produces nulls after the join

So table 1 has Client 1,2 & 3   And   Table 2 has Orders from Client 1, 2 and 8

(i.e. Client ID 8 produces nulls as it doesnt exist in the Client table)

The app I'm working on is quite complex and includes many tables from all over the place

into a snowflake model, but I was hoping to just get this basic test app right, and then apply the concept to my app.

I've done a basic qlikview app where I tried to 'fix' the nulls myself but couldnt get it right.

I tried to replicate this method from here How do I remove NULLs after a LEFT JOIN LOAD so I can select blank fields?

Where you create a mapping table and apply, however I wasnt successful.

Any help with this would be much appreciated.

Thank you

[Sergey_Shuklin]

Hello, Ruan!

If you don't need this null values at all you can use an Inner Join instead of Left Join.

[jcampbell474]

Hello!  Try adding the Where Exists statement to the second table in your load script.

Sales:

LOAD ClientID,

     SaleDate,

     Amount

FROM

(ooxml, embedded labels, table is B1)

Where Exists (ClientID);

[ruanhaese]

Hi.  Thanks guys for the help, but these both just hide the nulls

I.e. without the nulls the values total to 600, with the nulls it totals to 1,000

The correct value to display is 1000, but instead of the '-' character I'm trying to get a clickable 'SOMEWORD'

[jcampbell474]

It feels like you'll need to Left Join desired tables in the load script, then reload the combined table (and drop the previous) using something like if(Len(Trim(Name))=0,'Unlisted',Name) in each potential field.

Since you have a snowflake schema it could be an intensive task, so I would continue to look for an easier way.  Sorry I don't have an easy solution.

[atoz1158 · Accepted solution]

Hi

I have used the following type of code to change Nulls to something selectable

NullAsValue A,B;

Set NullValue = 'MISSING';

LOAD A,B from x.csv;

This can be adapted as required

Regards

Adrian

[jonathandienst]

You can make the null values clickable by using NULLASVALUE, like

     NULLASVALUE fieldname1, fieldname2

This effectively replaces the null with an empty string which will show up in a list box and be selectable.

Or you can use mapping to achieve the join, but you will need a mapping table for each field you want to add with the join. With the mapping, you can create a default value for the unmapped values, like 'Missing' or 'Unmapped'.

Create a mapping table from the second table using the join key and the value to add before loading the first table:

     MAP_CLIENTNAME:

     Mapping Load ClientID, ClientName

     FROM ....;   // second table from join

     Fact:   //first table from join

     LOAD ....
          ClientID,

          ApplyMap('MAP_CLIENTNAME', ClientID, 'Unmapped') as ClientName,

          ...

[ruanhaese]

This helped resolve the issue in the provided problem,

however I was unable to replicate it in a snowflake schema.

Thanks

[paolo_mapelli]

Hi, did you find any solution for this? Meaning let nulls from joins being selectable.

Regards.