If + IsNull doesn't work. Why?

[paweln]

left join (Table1)
Load "REF ZL", Wytop1, Wytop2, Wytop3, Wytop4, Wytop5,
if(IsNull(Wytop2)=-1 and IsNull(Wytop3)=-1 and IsNull(Wytop4)=-1 and IsNull(Wytop5)=-1,'1',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=-1 and IsNull(Wytop4)=-1 and IsNull(Wytop5)=-1,'2',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=0 and IsNull(Wytop4)=-1 and IsNull(Wytop5)=-1,'3',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=0 and IsNull(Wytop4)=0 and IsNull(Wytop5)=-1,'4',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=0 and IsNull(Wytop4)=0 and IsNull(Wytop5)=0,'5', 
))))) as "HowManyHeats"
Resident Table1;

Only last part works fine. I get:

[lironbaram · Accepted solution]

HI 

it doesn't work because when you do a left join if one of the common fields have nulls it doesn't work 

but as you do a resident load 

you can replace it with this part 

Table2:
Load *,
if(IsNull(Wytop2)=-1 and IsNull(Wytop3)=-1 and IsNull(Wytop4)=-1 and IsNull(Wytop5)=-1,'1',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=-1 and IsNull(Wytop4)=-1 and IsNull(Wytop5)=-1,'2',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=0 and IsNull(Wytop4)=-1 and IsNull(Wytop5)=-1,'3',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=0 and IsNull(Wytop4)=0 and IsNull(Wytop5)=-1,'4',
if(IsNull(Wytop2)=0 and IsNull(Wytop3)=0 and IsNull(Wytop4)=0 and IsNull(Wytop5)=0,'5', 
))))) as "HowManyHeats"
Resident Table1;

drop Table Table1;

[paweln]

Lironbaram,

Thank you, it works good!  Could you please explain why left join doesn't work here? What do you understand by "common fields"?