topic Multiplying/Dividing fields within QlikView script editor (Beginner Question) in QlikView

Multiplying/Dividing fields within QlikView script editor (Beginner Question)

Thu, 25 Feb 2010 14:03:33 GMT

Hi All

I'm new to this forum and to QlikView, I've searched for a similar post to my query but am unable to find one. This a simple question so hopefully someone can help.

I'm currently pulling in one table from one of our overseas office's database that contains the following fields, which are all of the ledger balances at each period month end:


[Ledger Balances]:
SQL SELECT
"nb_accode",
"nb_openbal",
"nb_period1",
"nb_period2",
"nb_period3",
"nb_period4",
etc…

The other data I'm pulling in is the € to £ Exchange rate for each of the Periods (standard excel table format):


[FX Rates]:
LOAD
MAY As FXrate[MAY],
JUN As FXrate[JUN],
JUL As FXrate[JUL],
AUG As FXrate[AUG],
etc...

What I want to do is divide each month end balance by the exchange rate and then sum the total for the year to date value in £. I know it's possible to do the calculations within the QlikView front end desktop, but I would rather do it within the script, as we have several overseas offices all running a separate database so I would just want 1 field called [*Office* Ledger Bals YTD] for each office. I'm sure the script will look something like this but I can't be sure as I'm very new to the QlikView scripting:


[Ledger Balances]:
SQL SELECT

"nb_accode",
("nb_openbal" / "FXrate[MAY]") +
("nb_period1" / "FXrate[MAY]") +
("nb_period2" / "FXrate[JUN]") +
("nb_period3" / "FXrate[JUL]") +
("nb_period4" / "FXrate[AUG]") +
("nb_period5" / "FXrate[SEP]") +
("nb_period6" / "FXrate[OCT]") +
("nb_period7" / "FXrate[NOV]") +
("nb_period8" / "FXrate[DEC]") +
("nb_period9" / "FXrate[JAN]") +
("nb_period10" / "FXrate[FEB]") +
("nb_period11" / "FXrate[MAR]") +
("nb_period12" / "FXrate[APR]") +
("nb_period13" / "FXrate[APR]") As [Berlin Ledger Bals YTD],

FROM Berlin.bdo.ledbals

Thanks in advance for your help.

Sam

Multiplying/Dividing fields within QlikView script editor (Beginner Question)

shumailh — Thu, 25 Feb 2010 14:34:20 GMT

Hello Sam,

To join both table you have to have an identical field, I design a sample of your code as fellow,


[Ledger_Balances]:
SQL SELECT
 [nb_accode],
 [nb_openbal],
 [nb_period1],
 [nb_period2],
 [nb_period3]
FROM Berlin.dbo.ledbals;
LEFT JOIN (Ledger_Balances)
LOAD
 [nb_accode],
 MAY As FXrate[MAY],
 JUN As FXrate[JUN],
 JUL As FXrate[JUL],
 AUG As FXrate[AUG]
FROM  (biff, embedded labels, table is Sheet1$);

LedBal:
LOAD
nb_accode,
(nb_openbal / FXrate[MAY]) +
(nb_period1 / FXrate[MAY]) +
(nb_period2 / FXrate[JUN]) +
(nb_period3 / FXrate[JUL]) +
(nb_period4 / FXrate[AUG]) as [Berlin Ledger Bals YTD]
RESIDENT Ledger_Balances;

drop table Ledger_Balances;

Regards,
Shumail Hussain

Multiplying/Dividing fields within QlikView script editor (Beginner Question)

Thu, 25 Feb 2010 15:34:36 GMT

Hello Shumail

Your answer worked first time, thank you again for the quick response.

Regards

Sam