2 Replies Latest reply: Oct 15, 2013 4:45 PM by Fernando Adam RSS

    JOIN - Calculated Fields.

      Hello to all .

      I'm starting with QlikView ... And I came across a problem .

       

      How do I do this?

       

      I have two tables :

       

      TABLE_CANCE:

      LOAD `Actual/Budget_SE`,

        `Regional_SE`,

        `Fábrica_SE`,

        `Indicador_SE`,

        SUM(`Jan-13_SE`) as `JAN_CANCE`,

        SUM(`Fev-13_SE`) as `FEV_CANCE`,

        SUM(`Mar-13_SE`) as `MAR_CANCE`

        WHERE `Indicador_SE` = 'Cancellations'

        GROUP BY `Actual/Budget_SE`, `Regional_SE`, `Fábrica_SE`, `Indicador_SE`;

      SQL SELECT *

      FROM `W:\Regionais\Financeiro\Análises\Base Resultado\Base SAP.accdb`.`qry_Sales to eLIS - QLICK`;

       

       

      TABLE_GROSS:

      LOAD `Actual/Budget_SE`,

        `Regional_SE`,

        `Fábrica_SE`,

        `Indicador_SE`,

        SUM(`Jan-13_SE`) as `JAN_GROSS`,

        SUM(`Fev-13_SE`) as `FEV_GROSS`,

        SUM(`Mar-13_SE`) as `MAR_GROSS`

        WHERE `Indicador_SE` = 'Gross Sales'

        GROUP BY `Actual/Budget_SE`, `Regional_SE`, `Fábrica_SE`, `Indicador_SE`;

      SQL SELECT *

      FROM `W:\Regionais\Financeiro\Análises\Base Resultado\Base SAP.accdb`.`qry_Sales to eLIS - QLICK`;

       

      And I need to do this:

       

      SELECT

          A.Actual/Budget_SE,

          A.Regional_SE,

          A.Fábrica_SE,

          A.Indicador_SE,

          SUM(A.JAN_GROSS) / SUM(B.JAN_CANCE) TOT_JAN,

          SUM(A.FEV_GROSS) / SUM(B.FEV_CANCE) TOT_FEV,

          SUM(A.MAR_GROSS) / SUM(B.MAR_CANCE) TOT_MAR

      FROM   TABLE_GROSS A, TABLE_CANCE B

      WHERE      A.Actual/Budget_SE = B.Actual/Budget_SE

                AND A.Regional_SE = B.Regional_SE

                AND A.Fábrica_SE = B.Fábrica_SE

      GROUP BY

          A.Actual/Budget_SE,

          A.Regional_SE,

          A.Fábrica_SE,

          A.Indicador_SE;

       

       

      How Do I?

       

      Thanks.

        • Re: JOIN - Calculated Fields.
          Stefan Wühl

          There are probably different approaches (one would be to use the DBMS to calculated the resulting table), but if you want to join within the QV backend:

           

          TMP:

          LOAD `Actual/Budget_SE`,

            `Regional_SE`,

            `Fábrica_SE`,

            `Indicador_SE`,

            SUM(`Jan-13_SE`) as `JAN_CANCE`,

            SUM(`Fev-13_SE`) as `FEV_CANCE`,

            SUM(`Mar-13_SE`) as `MAR_CANCE`

            WHERE `Indicador_SE` = 'Cancellations'

            GROUP BY `Actual/Budget_SE`, `Regional_SE`, `Fábrica_SE`, `Indicador_SE`;

          SQL SELECT *

          FROM `W:\Regionais\Financeiro\Análises\Base Resultado\Base SAP.accdb`.`qry_Sales to eLIS - QLICK`;

           

          LEFT JOIN (TMP) LOAD `Actual/Budget_SE`,

            `Regional_SE`,

            `Fábrica_SE`,

            `Indicador_SE`,

            SUM(`Jan-13_SE`) as `JAN_GROSS`,

            SUM(`Fev-13_SE`) as `FEV_GROSS`,

            SUM(`Mar-13_SE`) as `MAR_GROSS`

            WHERE `Indicador_SE` = 'Gross Sales'

            GROUP BY `Actual/Budget_SE`, `Regional_SE`, `Fábrica_SE`, `Indicador_SE`;

          SQL SELECT *

          FROM `W:\Regionais\Financeiro\Análises\Base Resultado\Base SAP.accdb`.`qry_Sales to eLIS - QLICK`;

           

          RESULT:

          LOAD `Actual/Budget_SE`,

              `Regional_SE`,

              `Fábrica_SE`,

              `Indicador_SE`,

              `JAN_CANCE` / `JAN_GROSS` as TOT_JAN,

              `FEV_CANCE` / `FEV_GROSS` as TOT_FEV,

              `MAR_CANCE` / `MAR_GROSS` as TOT_MAR

          Resident TMP;

           

          drop table TMP;

            • Re: JOIN - Calculated Fields.

              Thanks for the tip.

               

              I managed to do, but using the Outer Join ...

              And by making the division into a Straight Table.

               

              Owing to totalizers that change if I ever bring calculated.

               

               

              TMP:

              LOAD `Actual/Budget_SE`,

                `Regional_SE`,

                `Fábrica_SE`,

                SUM(`Jan-13_SE`) as `JAN_CANCE`,

                SUM(`Fev-13_SE`) as `FEV_CANCE`,

                SUM(`Mar-13_SE`) as `MAR_CANCE`

                WHERE `Indicador_SE` = 'Cancellations'

                AND `Actual/Budget_SE` = 'Actual'

                GROUP BY `Actual/Budget_SE`, `Regional_SE`, `Fábrica_SE`;

              SQL SELECT *

              FROM `...\Base SAP.accdb`.`qry_Sales to eLIS - QLICK`;

               

              OUTER JOIN (TMP)

              LOAD `Actual/Budget_SE`,

                `Regional_SE`,

                `Fábrica_SE`,

                SUM(`Jan-13_SE`) as `JAN_GROSS`,

                SUM(`Fev-13_SE`) as `FEV_GROSS`,

                SUM(`Mar-13_SE`) as `MAR_GROSS`

                WHERE `Indicador_SE` = Text('Gross Sales')

                AND `Actual/Budget_SE` = 'Actual'

                GROUP BY `Actual/Budget_SE`, `Regional_SE`, `Fábrica_SE`;

              SQL SELECT *

              FROM `...\Base SAP.accdb`.`qry_Sales to eLIS - QLICK`;

               

               

              In the Straight Table... Make the expression:

              Sum (JAN_CANCE)/Sum (JAN_GROSS)

               

              Works perfectly.

               

              Thanks for the help.

              I'm learning and really enjoying the tool...