2 Replies Latest reply: Jul 6, 2012 4:15 AM by Jason Michaelides RSS

    How to fill in blank or null values after outer join

      Hello

       

      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?

       

      Thanks

      Francois

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

          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.

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

            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:

             

            Map_Null_Unknown:

            MAPPING LOAD

                 Null()

                 ,'<Unknown>'

            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:

             

            RIGHT JOIN (Table) LOAD DISTINCT * RESIDENT Table;

             

            Hope this helps,

             

            Jason