Left Join with NULL on Script

[ElizaF]

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!

[Oleg_Troyansky · Accepted solution]

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

[ElizaF]

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.

[krishna_2644]

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

[prabhu0505]

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

[ElizaF]

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

[krishna_2644]

This way?

[krishna_2644]

ANy :Luck?

[ElizaF]

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?