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

    Exchange rate problem

      Hi,

       

      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

          Hi,

           

          You can do something like this:

           

          load

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

           

           

          succes!

           

          Halmar

          • Re: Exchange rate problem

            Hi,

             

            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).