topic Re: Join problem; Sum in the where clause in QlikView

Join problem; Sum in the where clause

Christian_Lauritzen — Wed, 09 Apr 2014 13:58:01 GMT

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;

Wed, 09 Apr 2014 14:04:18 GMT

left join (relations)

load leftkey2,rightkey2,sumdocs

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 — Wed, 09 Apr 2014 14:06:42 GMT

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

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