Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ElizaF
Not applicable

Left Join with NULL on Script

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:

     

F1F4ALTISNULLResult
1100100100100
2-1MISSING42290
3-1MISSING42290

See the file attached.

Could you please help?

Thank you in advance for help!

1 Solution

Accepted Solutions
Oleg_Troyansky
Not applicable

Re: Left Join with NULL on Script

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

8 Replies
Oleg_Troyansky
Not applicable

Re: Left Join with NULL on Script

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
Not applicable

Re: Left Join with NULL on Script

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
Not applicable

Re: Left Join with NULL on Script

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

prabhu0505
Not applicable

Re: Left Join with NULL on Script

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

AltFunctionIssues.PNG

ElizaF
Not applicable

Re: Left Join with NULL on Script

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

krishna_2644
Not applicable

Re: Left Join with NULL on Script

Capture1.PNG

This way?

krishna_2644
Not applicable

Re: Left Join with NULL on Script

ANy :Luck?

ElizaF
Not applicable

Re: Left Join with NULL on Script

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?