6 Replies Latest reply: Aug 20, 2014 7:24 PM by Marco Wedel RSS

    matching currency exchange rate

      Hi,

      I have a data table with a date and I have a currency exchange table with exchange rates for one date per month. I want to match the data table with the exchange rate of the respective month.

      I try to use IntervalMatch but I have no range only the beginning of the range. So I used to have something which maches with the nearest date before or same time as the date from my data table.

       

      Or I havt to add end values to my currency exchange table but I dont know how to do the by code.

       

      Has someone any ideas?

       

      hyharp

        • Re: matching currency exchange rate
          Srikanth P

          Hi Holly, As i understand correctly, you have one transnational table having Date. Other hand, you have exchange rate table having only one row per each month.

           

          Simply Join on the these tables  using MonthStart function on both fields.

           

          Transactions:

          LOAD *, MonthStart(Date) AS ER_KEY

          From Transactions;

           

          Exchange_Rate:

          Load Exchnage_Rate , MonthStart(Date) AS ER_KEY

          From ExchangeRate;

           

          So All the transactions under one month correctly tied to Exchange_Rate table.

          • Re: matching currency exchange rate
            Martyn Lloyd

            Hi, you can use Peek to create from and to dates in your exchange table, which will enable you to use intervalmatch

             

            ExchFromTo:
            LOAD
            *,
            RowNo() AS Row,
            If(RowNo() = 1, Null(),
              If(peek('Date', -1, 'ExchFromTo') >= Date,
               peek('Date', -1, 'ExchFromTo'), Date )) AS FromDate
            RESIDENT ExchangeRates
            ORDER BY Currency, Date;

            DROP Table ExchangeRates;

             

            Regards,

            M

            • Re: matching currency exchange rate
              Marco Wedel

              Hi hyharp,

               

              one solution could be:

               

              tabExchangeRates:
              LOAD * Inline [
              date start, exchange rate
              01/01/2014, 1.513
              02/01/2014, 1.456
              03/01/2014, 1.417
              03/15/2014, 1.348
              04/01/2014, 1.389
              04/10/2014, 1.391
              04/20/2014, 1.498
              05/01/2014, 1.568
              06/01/2014, 1.591
              06/25/2014, 1.643
              07/01/2014, 1.719
              08/01/2014, 1.737
              ];
              
              tabData:
              LOAD Date(Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1) as date,
                  Money(Ceil(Rand()*1000)) as amount
              AutoGenerate 1
              While Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1 <= Today();
              
              Left Join (tabExchangeRates)
              LOAD *, 
                  AutoNumberHash128([date start], [date end]) as %PeriodID;
              LOAD [date start], 
                  Alt(Date(Peek([date start])-1), Date(Today())) as [date end]
              Resident tabExchangeRates
              Order By [date start] desc;
              
              Left Join (tabData)
              IntervalMatch (date)
              LOAD [date start], [date end]
              Resident tabExchangeRates;
              
              Left Join (tabData)
              LOAD Distinct
                [date start],
                [date end],
                AutoNumberHash128([date start], [date end]) as %PeriodID
              Resident tabData;
              
              DROP Fields [date start], [date end] From tabData;
              

               

              QlikCommunity_Thread_130134_Pic3.JPG.jpg

              QlikCommunity_Thread_130134_Pic2.JPG.jpg

               

              QlikCommunity_Thread_130134_Pic1.JPG.jpg

               

              hope this helps

               

              regards

               

              Marco