Multiple Exchange Rates, generating

[#1]

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

[swuehl · Accepted solution]

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;

[#3]

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]

Right, I think it's mostly following

Creating a Date Interval from a Single Date

See also

IntervalMatch and Slowly Changing Dimensions