Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
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;
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...