Checking if field is null after outer join.

[#1]

Good Afternoon,

I'm trying to load values from two qvd files.

After that i want to make outer join and make a flag if field in outer table is set or not.

I'm trying to do it using isnull - as it works in SQL, but it's not working.

(see example. Insted qvd load I make inline load - to make easier example).

I know workaround - to join this values in SQL, but I want to understand where i make mistake in QlikView

(i want this logic to be on QlikView side).

Regards,
Greg

[adamdavi3s · Accepted solution]

Hi Greg,

The easiest way is just to joint the table back to itself:

JobCity:
LOAD * INLINE [
    JOB_ID, CITY
    1, New York
];
right join
LOAD * INLINE [
    JOB_ID, JOB_NAME
    1, Secretary
    2, Developer
    3, Manager
];


left join (JobCity)


load JOB_ID, if(len(CITY)<1,'Not set','Set') as CITY_FLAG
resident JobCity

[#3]

Thank you for this response.

Can you explain, why its not working in orginal example ?

Is QlikView calculating isnull value before joining, or what is reason for that.

[adamdavi3s]

I'm pretty sure its simply because the preceding load is only preceding the first table, if that makes sense!

You don't actually have the whole table as a single table until after the join etc

[#5]

More or less.

I thought preceding load are executed from bottom to top - so first there will be 2 load , then join , and then last new load.

[adamdavi3s]

Yeah I think I have made that assumption before as well... few funnies like that with preceding, such as distinct is only executed at the top level etc... still its very useful!