Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

Tags (3)
1 Solution

Accepted Solutions
Not applicable

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

2 Replies
MVP
MVP

Re: JOIN - Calculated Fields.

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;

Not applicable

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

Community Browser