2 Replies Latest reply: Sep 7, 2016 10:53 PM by Fernando Mercado RSS

    Hi, I'm having problems with NULLASVALUE, how can I fill the information of a certain ID with zero?

    Fernando Mercado

      I know the use of NULLASVALUE and I'm using it, but I don't know if qlikview isn't reading the variable or I'm I doing wrong, I think it's the second. This is the basic information, Thanks.

      Captura.PNG

      First Im setting the nullvalue and then the variables that I need.

      Captura.PNG

      Then in a loop I'm left Joining the data that I need, for every single year/month, someone know what I'm I doing wrong? Thanks

        • Re: Hi, I'm having problems with NULLASVALUE, how can I fill the information of a certain ID with zero?
          Nicole Smith

          NULLASVALUE will not populate missing data in a join, it will only populate values that are actually null.  Here is an example that may help explain this:

           

          SET NullValue ='0';
          NULLASVALUE C, D;
          
          
          Data:
          load *, if(mod(A,2)=0, 1) as C
          inline [
            A, B
            1, 1
            2, 2
            3, 3
            4, 4
          ];
          
          
          left join (Data)
          load *
          inline [
            C, D
            1, 2
          ];
          
          

           

          In this example, the values for C will be populated because there are actually rows for C.  However, with the join for D, there is actually missing data for two of the rows, so it will not populate those values.  You end up with a table that looks like this:

           

          A B C D
          110 -
          2212
          330
          4412

           

          You will need to populate the values a different way than what you're currently trying to do.  My suggestion would be that once you have everything in "Principalidad" (after everything is already joined), then do a NOCONCATENATE RESIDENT load of everything in it to a separate table, and use the NULLASVALUE then:

           

          Principalidad:
          LOAD Rut
          FROM [I:\Modelo Principalidad\Rutero.QVD] (QVD);
          
          Left Join (Principalidad)
          LOAD ...
          ...
          ...
          
          SET NullValue = '0';
          NULLASVALUE [Uso CTA], ..., ...;
          
          Principalidad_Final:
          NOCONCATENATE LOAD *
          RESIDENT Principalidad;
          
          DROP TABLE Principalidad;
          
          

           

          This should populate the data as expected.