left join adds extra rows to the table

[jduluc12]

Hi,

I have this script.

[Table1]:
LOAD * INLINE [
    F1, F2, F3
    123, 100, tere
    234, 200, we
    234, 300, rwerer
];


[Table2]:
LOAD * INLINE [
    F1, F4
    123, ter
    234
    234
    
];

left join (Table1)
load F1, F4
Resident Table2 where not IsNull(F4);

it results in the following table

it adds extra rows for 234. 

why is it happening and how can I prevent it. 

The desired output is 

123	100	tere	ter
234	200	we
234	300	rwerer

[justISO · Accepted solution]

Hi, seems you want to join only distinct values here, so try add this prefix, like:

left join (Table1)
load distinct
F1, F4 ...

In other hand, you can try to filter not isnull(), but rather empty F4, like:

... Resident Table2 where F4<>'';

as I believe using inline these empty/null values are treated somehow strange.