2 Replies Latest reply: Feb 25, 2010 10:34 AM by Samuel Sheldon RSS

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

      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