Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple Exchange Rates, generating

Hi all,

I have a question regarding multiple currencies from one source. Normally I have the source being a database, but for sample documents I stored those into a QVD.

I followed the documentation at Generating Missing Data In QlikView which provided to be very useful. However, this doesn't seem to work for multiple currencies.

I tried to do things like reloading tables for each currency, storing and loading them again. But this becomes very messy and I'm fairly sure there is a better alternative. Especially it would be nice if it is done dynamically so that whenever an additional currency pops up, the script doesn't need changing.

Basically it is the same issue as posted here Currency exchange issue (daily rates missing so... | Qlik Community starting from halfway through the discussion. Where multiple exchange rates are an issue.

Thanks,

Coen

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like this?

//Loads the Currency Rates, their date and the link to their name

[Rates]:

LOAD Currency_obj,

    ExchangeDate,

    ExchangeRate

FROM

[samplerates.qvd]

(qvd);

//Load Currency Names

[CurrencyNames]:

MAPPING

LOAD Currency_obj,

    CurrencyCode

FROM

[samplecurrencies.qvd]

(qvd);

Temp_Currency_Rates:

NoConcatenate load

  ApplyMap('CurrencyNames',"Currency_obj") as CurrencyCode,

  ExchangeDate,

  If(Previous(Currency_obj)=Currency_obj, Date(previous(ExchangeDate)-0.000001),today()) as ExchangeDateEnd,

  ExchangeRate

resident Rates

Order BY Currency_obj, ExchangeDate desc;

;

drop table Rates;

Rates:

LOAD CurrencyCode,

  Date(ExchangeDate+iterno()-1) as Date,

  ExchangeRate

RESIDENT Temp_Currency_Rates

WHILE ExchangeDate+iterno()-1 <= ExchangeDateEnd;

DROP TABLE Temp_Currency_Rates;

2016-05-17 11_58_10-QlikView x64 - [C__Users_Stefan_Downloads_Currencies_2.qvw].png

View solution in original post

3 Replies
swuehl
MVP
MVP

Maybe like this?

//Loads the Currency Rates, their date and the link to their name

[Rates]:

LOAD Currency_obj,

    ExchangeDate,

    ExchangeRate

FROM

[samplerates.qvd]

(qvd);

//Load Currency Names

[CurrencyNames]:

MAPPING

LOAD Currency_obj,

    CurrencyCode

FROM

[samplecurrencies.qvd]

(qvd);

Temp_Currency_Rates:

NoConcatenate load

  ApplyMap('CurrencyNames',"Currency_obj") as CurrencyCode,

  ExchangeDate,

  If(Previous(Currency_obj)=Currency_obj, Date(previous(ExchangeDate)-0.000001),today()) as ExchangeDateEnd,

  ExchangeRate

resident Rates

Order BY Currency_obj, ExchangeDate desc;

;

drop table Rates;

Rates:

LOAD CurrencyCode,

  Date(ExchangeDate+iterno()-1) as Date,

  ExchangeRate

RESIDENT Temp_Currency_Rates

WHILE ExchangeDate+iterno()-1 <= ExchangeDateEnd;

DROP TABLE Temp_Currency_Rates;

2016-05-17 11_58_10-QlikView x64 - [C__Users_Stefan_Downloads_Currencies_2.qvw].png

Not applicable
Author

Cool, that seems to work to create currencies for all missing dates indeed. The equal to the previous obj is main factor in this I believe. So that it only matches the same currency. Nice, thank you.

swuehl
MVP
MVP