Join problem; Sum in the where clause

[Christian_Lauritzen]

Ideas why the join below does not work?

I suspect that I cannot use sum in the where clause, but hopefully I am wrong and there is another explanation. Would appreciate any hints!

Left join(Relations) LOAD Distinct

  relKey,

  FirstValue(tmp_rKey) as LeftKey2,

  FirstValue(tmp_sKey) as RightKey2

Resident Relations

where sum(docs) = 0

group by relKey;

[#2]

left join (relations)

load leftkey2,rightkey2,sumdocs

k:

load distinct

   relkey, fistvalue(tmp_rkey) as leftkey2,

   firstvalue(tmp_skey)as Rightkey2, sum(docs) as sumdocs

resident relations

group by relkey;

left join(relatins)

load leftkey2,rightkey2,sumdocs resident k where sumdocs >0;

drop table k;

[Christian_Lauritzen · Accepted solution]

Solved it by moving the sum condition to an if-clause on the line;

     if(sum(docs)>0,tmp_rKey) as LeftKey2    ... etc