JOIN - Calculated Fields.

[#1]

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.

[swuehl]

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;

[#3 · Accepted solution]

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...