topic JOIN - Calculated Fields. in QlikView

JOIN - Calculated Fields.

Tue, 15 Oct 2013 13:17:32 GMT

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.

swuehl — Tue, 15 Oct 2013 14:09:50 GMT

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.

Tue, 15 Oct 2013 20:45:34 GMT

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