Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
Hi Ronald,
i have attached the application now. Thank you for trying
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;
IT works just as i wanted. Ronald, thank you soo much for your help ! I really appreciate it! You seriously just saved my weekend !
Thaaaaank you !
Great to hear. Although this is exciting stuff and all, I'm certain you'll have better things to do in your weekend. Enjoy.
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
You might want to change:
Order By CURRENCYCODE,FROMDATE desc;
into
Order By CURRENCYCODE,FROMDATE asc;
It did the trick! I have to read up on that Thank you !
Great to hear it worked.
It's not really that complicated. We changed the order in which we work through all dates (picking the exchange rate of the previous day if empty) from descending (so 31-12, 30-12, 29-12) to ascending (so 29-12, 30-12, 31-12).