3 Replies Latest reply: Oct 24, 2011 6:38 AM by Halmar Heijnen RSS

    Exchange rate problem



      I have problem with exchange rate. I have two tables A (order_number, order_date, currency) and B (date, currency, exchange_rate). I created key (date&currency) and it's working perfectly, but..


      On each Monday I don't have exchange rate value, becaus it's announced on Tuesday. In this case I sholud use exchange rate from last day when it was announced (in this case from Friday). How to do this?


      I need this value when I create a straight table and I'm converting currency:


      if(Currency<>'PLN',[Ordered quantity]*[Purchase price]*[Exchange rate];[Ordered quantity]*[Purchase price])

        • Exchange rate problem



          You can do something like this:



               if (isnull(exchangerate) and day=monday,above(exchangerate,3),exchangerate) as exchangerate






          • Re: Exchange rate problem



            function above() is not working in script, but I can use it in straight table.


            I have one more problem. I noted that if in tabel with EX_RATE exchange rate don't exist then I can't use it as key.

            Please, look to example below. In straight table 'ORDER VALUE' I would like to have position for date 20111017 and currency EUR,

            which consist in column exchange rate value from day before (in this case from 20111014).