Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


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.

Not applicable

How to fill in blank or null values after outer join


What is the best practice approach to managing blank or null values that "appear" to be generated as a result of outer joins in Qlikview?

What I normally do is once all data has been loaded after the join, I then go and load resident from the original table, and then do a test and replace the nulls with something like a 0 or Y or similar. I then drop the original table. I have also seen users mention that using applymap is an option but I dont see that working as well.

Any ideas?



2 Replies
Not applicable

Re: How to fill in blank or null values after outer join

Why do you want to replace blank fields? There is an option in the charts where you can choose the character/s to repalce blanks and you can do the same thing calculating the dimensions you are going to use so you don´t need to replace all the blank fields but only what you need.

Not applicable

Re: How to fill in blank or null values after outer join

I don't like having any NULLs in my data model as they are difficult to work with and not selectable.  I normally replace NULLs with '<Unknown>' or something similar.

MAP...USING works well:





Autogenerate 1;

MAP Field1,Field2,Field3,etc USING Map_Null_Unknown;

Now all nulls listed in the MAP...USING statement will be replaced as they are loaded.  However, nulls created by LEFT/OUTER joins will not be replaced, unless that field is subsequently loaded later in the script. If your joined fields with nulls are not loaded again (by some kind of resident load) then you will need to force it.  This works well:


Hope this helps,