topic Re: Currency tabel in QlikView

Currency tabel

vilstrup — Fri, 16 Jun 2017 07:46:26 GMT

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

Re: Currency tabel

RonaldDoes — Fri, 16 Jun 2017 07:55:05 GMT

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

vilstrup — Fri, 16 Jun 2017 08:02:41 GMT

Hi Ronald,

i have attached the application now. Thank you for trying

Re: Currency tabel

RonaldDoes — Fri, 16 Jun 2017 12:02:29 GMT

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

vilstrup — Fri, 16 Jun 2017 12:10:43 GMT

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 !

Re: Currency tabel

RonaldDoes — Fri, 16 Jun 2017 12:13:41 GMT

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

Re: Currency tabel

vilstrup — Tue, 20 Jun 2017 13:12:37 GMT

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

RonaldDoes — Tue, 20 Jun 2017 13:18:51 GMT

You might want to change:

Order By CURRENCYCODE,FROMDATE desc;

into

Order By CURRENCYCODE,FROMDATE asc;

Re: Currency tabel

vilstrup — Tue, 20 Jun 2017 13:24:59 GMT

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

Re: Currency tabel

RonaldDoes — Tue, 20 Jun 2017 13:44:47 GMT

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