Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
igdrazil
Creator III
Creator III

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

11 Replies
sunny_talwar

Try this may be:

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

sinanozdemir
Specialist III
Specialist III

Hi Chris,

Maybe try something like this:

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

Hope this helps.

igdrazil
Creator III
Creator III
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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)

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
igdrazil
Creator III
Creator III
Author

Hi Jonathan,

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

Regards

Chris

sunny_talwar

Would you be able to share a sample?

sinanozdemir
Specialist III
Specialist III

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

igdrazil
Creator III
Creator III
Author

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?