topic Re: Left Join with NULL on Script in QlikView

Left Join with NULL on Script

ElizaF — Tue, 13 Oct 2015 15:04:44 GMT

Hello Team,

I have two tables join with left join, and the null values must be filled with calculated values.

I tried to use the ISNULL () function and ALT() function, but without success.

I think it's from setting, but do not understand where is my mistake.

My desired result it is:

F1	F4	ALT	ISNULL	Result
1	100	100	100	100
2	-	1	MISSING	42290
3	-	1	MISSING	42290

See the file attached.

Could you please help?

Thank you in advance for help!

Re: Left Join with NULL on Script

Oleg_Troyansky — Tue, 13 Oct 2015 15:39:34 GMT

Hi Eliza,

the null values that you are getting are caused by a mismatch of the JOIN keys during the joining process. Within the JOIN load, these values can't be tested for NULL yet. Technically speaking, they are not NULL, they are MISSING.

In order to overcome this issue, you need to reload your resulting table one more time, using a RESIDENT load, and replace nulls with the desired values. You can also use more elegant NULL handling techniques such as NullAsValue. I describe null handling techniques, along with many other advanced scripting techniques, in my new book "QlikView Your Business".

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Left Join with NULL on Script

ElizaF — Tue, 13 Oct 2015 16:39:32 GMT

Hi Oleg,

Thank you for the answer. It is very correct what you said.

I hope to find another solution not to reload again the resulting table one more time.

I will read the book, thanks for advises.

Re: Left Join with NULL on Script

Qrishna — Tue, 13 Oct 2015 16:54:21 GMT

do you wanna fill the nulls or missing values with num(today()) or 42290 in your grid?

Re: Left Join with NULL on Script

prabhu0505 — Tue, 13 Oct 2015 17:01:46 GMT

Agree with Oleg, just applied the same logic to your dashboard.

Re: Left Join with NULL on Script

ElizaF — Tue, 13 Oct 2015 17:07:08 GMT

Correct value for null / missing values is num(month(today())) and base of this will be calculated another value..

Re: Left Join with NULL on Script

Qrishna — Tue, 13 Oct 2015 18:14:39 GMT

This way?

Re: Left Join with NULL on Script

Qrishna — Fri, 16 Oct 2015 14:56:57 GMT

ANy :Luck?

Re: Left Join with NULL on Script

ElizaF — Wed, 21 Oct 2015 09:39:31 GMT

Thanks everyone for your help.

I found, also, another solution for my issue using the "Exists" and Not Exists" function.

I am attaching the file where in the CONCATENATE TAB you can look on my solution.

What do you think?