9 Replies Latest reply: Jun 20, 2017 9:44 AM by Ronald van der Does RSS

    Currency tabel

    Nicolai Vilstrup

      Hi,

       

      I have been sitting with a problem for a week now. I can't seem to find the solution by myself, so I am hoping one of you guys could help me out.

       

      I am loading data from an axapta 2009 extract database and I am trying to convert my inventrans to the right currency.

       

      My problem is that the EXCHRATES table only has rows from when the rate has changed. That means that I can't key my inventrans to my exchangerate and there by multiply to get the right balance.

       

      i have search the internet and found some different approaches. The best one i found was this:

       

      OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Ax2009Extract;Data Source=dkrmed465\dkrmed465;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LAP0000264;Use Encryption for Data=False;Tag with column collation when possible=False];

       

       

      TempTable_Rates:

      LOAD

      Date(FROMDATE,'YYYY-MM-DD') as FROMDATE,

       

         EXCHRATE,

          CURRENCYCODE,

          DATAAREAID

       

       

          Where DATAAREAID like 'rme'  // This is to get the currency to danish krones.

          and Year(FROMDATE) >= '2015'

          and CURRENCYCODE = 'USD' ; // If i comment this out it doesent work.

       

       

      SQL SELECT

      FROMDATE,

          EXCHRATE,

          TODATE,

          DATAAREAID,

          CURRENCYCODE,

          DATAAREAID

       

       

       

       

      FROM Ax2009Extract.dbo.EXCHRATES;

       

      MinMaxDate:

      Load

      Min(FROMDATE) As MinDate, Max(FROMDATE) As MaxDate resident TempTable_Rates;

      Let vMinDate = Peek('MinDate',-1,'MinMaxDate')-1;

      Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

       

       

      Join (TempTable_Rates)

           Load Date(recno()+($(vMinDate))) as FROMDATE Autogenerate vMaxDate - vMinDate;

       

       

       

      Rates:

      NoConcatenate Load

      //Denne laver en nøgle til Danaherkalenderen

      Date(FROMDATE,'YYYY-MM-DD') as FROMDATE,

       

       

      if( IsNull(EXCHRATE), Peek(EXCHRATE),EXCHRATE) as EXCHRATE,

      if( IsNull(CURRENCYCODE), Peek(CURRENCYCODE),CURRENCYCODE)as CURRENCYCODE,

      if( IsNull(DATAAREAID), Peek(DATAAREAID),DATAAREAID)as DATAAREAID

       

       

       

      Resident TempTable_Rates

      Order By FROMDATE;

       

       

      Drop Table MinMaxDate, TempTable_Rates;

       

       

      My problem with this is that it only works when i am loading one currency. In this example USD.

      The code populates all the dates in between and adds the latest currency to these dates which are exactly what i need.

      But when i am loading all the different currencies it doesn't work. as shown on the pictures below. Does anyone have a solution to my problem?

      Can i load all the different currencies and then join them together or could i add something to the code so that it will populate all the dates for all the different currency codes?

       

      Thank you in advance all of you

      currency with only USD.PNG

      currency with all.PNG

        • Re: Currency tabel
          Ronald van der Does

          Hi Nicolai,

           

          If you order by CURRENCYCODE, FROMDATE wouldn't it work?

          Could you share an example QVW so I can try to help you solve this in there?

           

          With kind regards,

          Ronald

            • Re: Currency tabel
              Nicolai Vilstrup

              Hi Ronald,

               

              i have attached the application now. Thank you for trying

                • Re: Currency tabel
                  Ronald van der Does

                  Hi Nicolai,

                   

                  Since of course I do not have access to your OLEDB connection, I had to make up some data. However, you should be able to uncomment your script and comment mine, and it should work. For reference however, I have attached my sample data.

                   

                  Furthermore, I borrowed the excellent solution to your problem by martinpohl here: Filling the missing dates for exchange rates

                   

                  LOAD Date(FROMDATE,'YYYY-MM-DD') as FROMDATE,
                       EXCHRATE, 
                       CURRENCYCODE, 
                       DATAAREAID
                  FROM
                  Demo.xlsx
                  (ooxml, embedded labels, table is Fixed)
                  Where DATAAREAID = 'rme' and Year(FROMDATE) >= '2015';
                  
                  
                  MinMaxDate:
                  Load Min(FROMDATE) as MinDate, Max(FROMDATE) as MaxDate resident TempTable_Rates;
                  Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
                  Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
                  Drop Table MinMaxDate;
                  
                  Date_Currency:
                  Load Date(recno()+$(vMinDate)) as FROMDATE Autogenerate vMaxDate - vMinDate;
                  //here join all currency from your datas
                  left join load distinct CURRENCYCODE resident TempTable_Rates;
                  //join all dates and all currencys to datas
                  outer Join (TempTable_Rates) load * resident Date_Currency;
                  //drop temporary table
                  drop table Date_Currency;
                  Rates:
                  NoConcatenate Load FROMDATE,
                  If( IsNull( EXCHRATE ),if(peek(CURRENCYCODE)=CURRENCYCODE,Peek( EXCHRATE ), EXCHRATE ),EXCHRATE) as EXCHRATE,CURRENCYCODE
                  
                  Resident TempTable_Rates
                  Order By CURRENCYCODE,FROMDATE desc ;
                  Drop Table TempTable_Rates;
                  
              • Re: Currency tabel
                Nicolai Vilstrup

                Hi Ronald. I went through the data today and it seems it takes the wrong exchange rate to the dates.

                 

                I want it to take the last updated exchange rate and fill the empty cell.

                 

                fx.

                if I am looking at July 2015, there was to exchange rate updates.

                2015-07-04 at 746,0712

                2015-07-31 at 746,2636

                 

                In my table, the dates in between these two are 746,2636 when it should have been 746,0712.

                Do you know where it goes wrong?

                 

                 

                Again thank you for your help

                • Re: Currency tabel
                  Nicolai Vilstrup

                  It did the trick! I have to read up on that Thank you !