Filter Null after Join

[igdrazil]

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

[sunny_talwar]

Try this may be:

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

[sinanozdemir]

Hi Chris,

Maybe try something like this:

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

Hope this helps.

[igdrazil]

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]

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)

[jonathandienst]

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.

[igdrazil]

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]

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

[igdrazil]

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?