6 Replies Latest reply: May 15, 2017 11:32 AM by Rafael Souza RSS

    Working with NULLs in LEFT JOIN (Script)

    Rafael Souza

      Guys, good afternoon,


      I'm having a problem working with null fields in QlikView:
      I join a table coming from SYBASE with another one coming from Excel as script below:


      SELECT

      'LSG' AS 'PRODUTO',

      'COMERCIAL VEICULO'  AS 'CANAL',

      ct.CdOperador AS 'CDCOLABORADOR',

      fl.NmRazaoSocial AS 'NMFILIAL',

      COUNT(*) AS 'CARTEIRA',

      2 AS 'TPQUERY',

      CONVERT(date, getdate()) AS 'DTFOTO'

      FROM

      DBGESTAOLSG..TbContrato ct,

      DBCOR..TbFilial fl

      WHERE

        ct.CdFilial = fl.CdFilial

      AND ct.DtLiquidacaoEfetiva IS NULL

      AND ct.DtCancelamento IS NULL

      AND fl.DsFilial NOT LIKE 'CP%'

      AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

      GROUP BY ct.CdOperador, fl.NmRazaoSocial;

       

      LEFT JOIN (TB_CPVEIC)

      TBHIERARQUIA_CARTCPVEIC:

      LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

          IF(IsNull(NOME_REGIONAL), 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

      FROM

      [M:\GARANTIAS E FORMALIZAÇÃO\Remuneração\2017\Hierarquia\Base_Hierarquia Comercial - Mar17.xlsx]

      (ooxml, embedded labels, table is Base_Hierarquia);


      According to script, I wish that AFTER joining the 2 tables (LEFT JOIN) the null values:

      Table.png

      Instead of being Null as picture above, be defined as 'Not Localized'


      Could you help me solve this problem?

      Thanks in advance!

        • Re: Working with NULLs in LEFT JOIN (Script)
          Rafael Souza

          In time, the complete script is this:


          TB_CPVEIC:

          SELECT

          'LSG' AS 'PRODUTO',

          'COMERCIAL VEICULO'  AS 'CANAL',

          ct.CdOperador AS 'CDCOLABORADOR',

          fl.NmRazaoSocial AS 'NMFILIAL',

          COUNT(*) AS 'CARTEIRA',

          2 AS 'TPQUERY',

          CONVERT(date, getdate()) AS 'DTFOTO'

          FROM

          DBGESTAOLSG..TbContrato ct,

          DBCOR..TbFilial fl

          WHERE

            ct.CdFilial = fl.CdFilial

          AND ct.DtLiquidacaoEfetiva IS NULL

          AND ct.DtCancelamento IS NULL

          AND fl.DsFilial NOT LIKE 'CP%'

          AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

          GROUP BY ct.CdOperador, fl.NmRazaoSocial;

           

          LEFT JOIN (TB_CPVEIC)

          TBHIERARQUIA_CARTCPVEIC:

          LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

          IF(IsNull(NOME_REGIONAL), 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

          FROM

          [M:\GARANTIAS E FORMALIZAÇÃO\Remuneração\2017\Hierarquia\Base_Hierarquia Comercial - Mar17.xlsx]

          (ooxml, embedded labels, table is Base_Hierarquia);

          • Re: Working with NULLs in LEFT JOIN (Script)
            Sunny Talwar

            You will have to add a resident load to fix this

             

            TB_CPVEIC:

            SELECT

            'LSG' AS 'PRODUTO',

            'COMERCIAL VEICULO'  AS 'CANAL',

            ct.CdOperador AS 'CDCOLABORADOR',

            fl.NmRazaoSocial AS 'NMFILIAL',

            COUNT(*) AS 'CARTEIRA',

            2 AS 'TPQUERY',

            CONVERT(date, getdate()) AS 'DTFOTO'

            FROM

            DBGESTAOLSG..TbContrato ct,

            DBCOR..TbFilial fl

            WHERE

              ct.CdFilial = fl.CdFilial

            AND ct.DtLiquidacaoEfetiva IS NULL

            AND ct.DtCancelamento IS NULL

            AND fl.DsFilial NOT LIKE 'CP%'

            AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

            GROUP BY ct.CdOperador, fl.NmRazaoSocial;

             

            LEFT JOIN (TB_CPVEIC)

            TBHIERARQUIA_CARTCPVEIC:

            LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

                NOME_REGIONAL

            FROM

            [M:\GARANTIAS E FORMALIZAÇÃO\Remuneração\2017\Hierarquia\Base_Hierarquia Comercial - Mar17.xlsx]

            (ooxml, embedded labels, table is Base_Hierarquia);

             

            Final_TB_CPVEIC

            LOAD *,

                  If(Len(Trim(NOME_REGIONAL)) = 0, 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

            Resident TB_CPVEIC;

             

            DROP Table TB_CPVEIC;

            • Re: Working with NULLs in LEFT JOIN (Script)
              omar bensalem

              do a mapping load instead in that case:


              TBHIERARQUIA_CARTCPVEIC:

              MAPPING LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

                  IF(IsNull(NOME_REGIONAL), 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

              FROM

              [M:\GARANTIAS E FORMALIZAÇÃO\Remuneração\2017\Hierarquia\Base_Hierarquia Comercial - Mar17.xlsx]

              (ooxml, embedded labels, table is Base_Hierarquia);

               

               

              load *,

              applyMap('TBHIERARQUIA_CARTCPVEIC',CDCOLABORADOR,'Not Localized') as RegionalField

              ;

              SELECT

              'LSG' AS 'PRODUTO',

              'COMERCIAL VEICULO'  AS 'CANAL',

              ct.CdOperador AS 'CDCOLABORADOR',

              fl.NmRazaoSocial AS 'NMFILIAL',

              COUNT(*) AS 'CARTEIRA',

              2 AS 'TPQUERY',

              CONVERT(date, getdate()) AS 'DTFOTO'

              FROM

              DBGESTAOLSG..TbContrato ct,

              DBCOR..TbFilial fl

              WHERE

                ct.CdFilial = fl.CdFilial

              AND ct.DtLiquidacaoEfetiva IS NULL

              AND ct.DtCancelamento IS NULL

              AND fl.DsFilial NOT LIKE 'CP%'

              AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

              GROUP BY ct.CdOperador, fl.NmRazaoSocial;

              • Re: Working with NULLs in LEFT JOIN (Script)
                Rafael Souza

                I used the Script option and it worked, Once again, thanks for the help !!

                • Re: Working with NULLs in LEFT JOIN (Script)
                  Rafael Souza

                  I marked the answer as Helpful, Thank you