topic Re: Multiple Exchange Rates, generating in QlikView

Multiple Exchange Rates, generating

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Multiple Exchange Rates, generating

swuehl — Tue, 17 May 2016 09:58:02 GMT

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;

Re: Multiple Exchange Rates, generating

Tue, 17 May 2016 10:25:11 GMT

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.

Re: Multiple Exchange Rates, generating

swuehl — Tue, 17 May 2016 10:29:53 GMT

Right, I think it's mostly following

Creating a Date Interval from a Single Date