Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vilstrup
Contributor III
Contributor III

Currency tabel

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

1 Solution

Accepted Solutions
RonaldDoes
Partner - Creator III
Partner - Creator III

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;

View solution in original post

9 Replies
RonaldDoes
Partner - Creator III
Partner - Creator III

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

vilstrup
Contributor III
Contributor III
Author

Hi Ronald,

i have attached the application now. Thank you for trying

RonaldDoes
Partner - Creator III
Partner - Creator III

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;

vilstrup
Contributor III
Contributor III
Author

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 !

RonaldDoes
Partner - Creator III
Partner - Creator III

Great to hear. Although this is exciting stuff and all, I'm certain you'll have better things to do in your weekend. Enjoy.

vilstrup
Contributor III
Contributor III
Author

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

RonaldDoes
Partner - Creator III
Partner - Creator III

You might want to change:


Order By CURRENCYCODE,FROMDATE desc;


into


Order By CURRENCYCODE,FROMDATE asc;

vilstrup
Contributor III
Contributor III
Author

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

RonaldDoes
Partner - Creator III
Partner - Creator III

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