11 Replies Latest reply: Dec 1, 2015 9:53 AM by Christoph Hebig

# Filter Null after Join

Hi There,

I got a problem with my Set Analysis. At first, i have 2 tables that i join. 1 is looking like this: a, b, c and the second one is looking like this a, b, c, d. So when I join these 2 i get a table with a, b , c, d (simplified). NOW, when i try to sum(a) i will get a lot more than expected because there are the values of column a of the second table too. The only way how i could filter them is by set analyzing column d. I want all the values of sum(a) where d has no value -> NULL.

I tried to do something like this: sum({\$<d -= {'*'}>}a) but i'll get no values.

Does anyone have any idea how to do this?

FYI column a,d has integer values in it, b is a timestamp and c got some Strings

Regards

Chris

• ###### Re: Filter Null after Join

Try this may be:

=Sum({<d = {"=Len(Trim(d)) = 0"}>}a)

• ###### Re: Filter Null after Join

Hi Chris,

Maybe try something like this:

sum({1-<d = {'*'}>}a)

Hope this helps.

• ###### Re: Filter Null after Join

Hi, first of all thanks for you fast answers.

Second: Sadly both don't work, Sunny's version will give me an empty chart where the x-axis shows " - "

Sinan's version will give me a few dots above each other and same outcome on the x-axis.

Regards

Chris

• ###### Re: Filter Null after Join

The best way to handle this is to convert the nulls into selectable values. use the command

NULLASVALUE d;

before the join.

Then you can select them like:

Sum({<d = {''}>} a)

• ###### Re: Filter Null after Join

But I am concerned about your data model. Do you need to join? You will get a synthetic key on a,b,c, but this is a legitimate composite key in your case.

Or else concatenate the two tables rather than joining them.

• ###### Re: Filter Null after Join

Hi Jonathan,

i tried the Nullasvalue thing but i still can't select the needed values.

Regards

Chris

• ###### Re: Filter Null after Join

Are the values truly NULL? Maybe they are just blank.

• ###### Re: Filter Null after Join

As far as i know, missing values will be handled as NULL, when joining 2 tables (but i could be wrong with this)

Else, if they are blank how would i filter by blank then?

• ###### Re: Filter Null after Join

If you want to exclude blank values, then sum({<d = {"<>''"}>}a)

or

If you want to count the blank values, then sum({<d = {''}>}a)

• ###### Re: Filter Null after Join

From Qlik Help Site:

When no data can be produced for a certain field as a result of a database query and/or a join between tables, the result is normally a NULL value.

Regards

Chris

• ###### Re: Filter Null after Join

Would you be able to share a sample?