topic Re: Checking if field is null after outer join. in QlikView

Checking if field is null after outer join.

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Checking if field is null after outer join.

adamdavi3s — Mon, 20 Feb 2017 12:11:30 GMT

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

Re: Checking if field is null after outer join.

Mon, 20 Feb 2017 12:21:38 GMT

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.

Re: Checking if field is null after outer join.

adamdavi3s — Mon, 20 Feb 2017 12:33:14 GMT

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

Re: Checking if field is null after outer join.

Wed, 22 Feb 2017 10:40:40 GMT

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.

Re: Checking if field is null after outer join.

adamdavi3s — Wed, 22 Feb 2017 10:44:59 GMT

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!