Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Removing Nulls (from after Joins)

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

1 Solution

Accepted Solutions
Creator II
Creator II

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

View solution in original post

8 Replies
Specialist
Specialist

Hello, Ruan!

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

Creator III
Creator III

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

Partner
Partner

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'

Creator III
Creator III

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.

Creator II
Creator II

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

View solution in original post

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,

          ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

This helped resolve the issue in the provided problem,

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

Thanks

Creator II
Creator II

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

Regards.