1 Reply Latest reply: Dec 1, 2009 1:28 PM by Linda Monincx RSS

    Is there a smart way to multiply by a given currencyrate?

    Andreas Karlsson

      Hi folks,

      First of all this code has to work on Qlikview 7, by that time there was no input-field in Qlikview.

      I have a databasetable with transactions and they are labeled with different currencies like this

      amount currency

      71 SEK

      23 NOK

      54 NOK

      ....

      The problem is to convert all of them to the same currency, either SEK or NOK in this example.

      The currency rate shall be given in a Excelfile.perhaps like this

      NOKToSEKRate SEKToNOKRate

      1,26 0,79

      I understand that I can solve the problem by adding an expression with an if-statement in every sheetobject and calculate the correct amount during runtime but I would better like a solution which takes the calculation overhead during script-execution time and produces maybe one column named amount_SEK and another named amount_NOK.

      One solution could be if the rates could be assigned into a variable with the SET - statement but I cannot find a way to read the number 1,26 or 0.79 into a variable from the xls-file.

      Another way I thought of would be to join the two tables and then while reloading from that new resident an IF-statement could be used to multiply with the rates but unfortunately the joined resident is just never created by Qlikview - I don't see why.

      What would be a nice solution according to you guys?

      Here is my try with two small example xls-files

      /Andreas

      [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/5710.trasactions.xls:550:0]

      [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/3644.rates.xls:550:0]

      RATES:
      LOAD NOKToSEKRate,
      SEKToNOKRate
      FROM
      rates.xls
      (biff, embedded labels, table is Sheet1$);


      TRANS:
      LOAD amount,
      currency
      // IF(currency='NOK',amount*NOKToSEKRate,amount) as amount_SEK does not work
      FROM
      trasactions.xls
      (biff, embedded labels, table is Sheet1$);


      TEST1_tmp: //this resident is never created by Qlikview....by some reason?!?
      LOAD *
      RESIDENT RATES;
      JOIN
      LOAD *
      RESIDENT TRANS;


      //TEST1: //so this cannot be used
      //LOAD
      // amount,
      // currency,
      // IF(currency='NOK',amount*NOKToSEKRate,amount) as amount_SEK,
      // IF(currency='SEK',amount*SEKToNOKRate,amount) as amount_NOK
      //RESIDENT TEST1_tmp;
      //DROP TABLE TEST1_tmp

        • Is there a smart way to multiply by a given currencyrate?
          Linda Monincx

          hi,

           

          I think the issue of the join is that you join on *, try to list the fields with the first field for the join and see if that table is being created. In case you create a tabel wich exactly has the same columns as an earlier table loaded, it's beeing appended to the first table so not creating the second table seperate, you can avoid this by adding an extra column like '1' as counter and delete this field later on in the script.

          I think i would try the if statement.

          Hope this solves your issue.

          Regards, Linda