Organizations dealing in multiple currencies may use a web service to extract the exchange rates. They may even store the currency exchange rates in Excel files or sometimes in a database table. The exchange rates for any currency may be stored only for each RateStartDate that is for the day when the rate changes its value. However, for our reporting purposes we need exchange rates for each day and not just for the day when the rate changes. For this purpose, it is beneficial to create an Exchange Rate Calendar.
Create a new Qlik Sense application and load the following script into your Qlik Sense application:
ExchangeRatetemp: LOAD FromCurrency,ExchangeRate, DATE(Date#(RateStartDate,'DD/MM/YYYY')) as RateStartDate INLINE [ FromCurrency, ExchangeRate, RateStartDate EUR,0.687,01/08/2012 EUR,0.757,02/09/2012 EUR,0.74,08/09/2013 EUR,1.10,24/10/2014 SGD,0.52,01/08/2012 SGD,0.68,27/02/2014 SGD,0.88,28/03/2015 USD,0.75,14/12/2013 USD,0.77,16/01/2014 USD,0.85,26/06/2015 ];
We will now generate the end dates for each currency exchange rate:
- Load the following script to generate the
RateEndDatefor each exchange rate:ExchangeRate: LOAD FromCurrency, ExchangeRate, Date (RateStartDate) AS RateStartDate, If (FromCurrency=Peek (FromCurrency), Date (Peek (RateStartDate)-1), Today ()) AS RateEndDate RESIDENT ExchangeRatetemp ORDER BY FromCurrency, RateStartDate DESC; DROP TABLE ExchangeRatetemp;
- Go to the App overview window and open a new sheet.
- Enter the Edit mode by clicking on
. - Drag the Table object onto the screen and add all the four dimensions to it. Promote RateStartDate to the top of the sorting order and set the sort order as numeric ascending.
- The result would be as follows:
- As we can see, every record for a currency now has a rate end date.
- We will now use the
RateStartDateandRateEndDatefields as our base dates for the Exchange Rate Calendar. - Now, copy and paste the following script after the
DROP TABLE ExchangeRatetempstatement://------------------------------------------------- // Generate calendar dates //------------------------------------------------- LET ExStartDate = Num(Peek('RateStartDate', -1, ExchangeRate)); LET ExEndDate = Num(Peek('RateEndDate', 0, ExchangeRate)); ExchangeRateCalendar: LOAD Date($(ExStartDate) + RecNo() - 1) AS ExchangeRateDate AUTOGENERATE ($(ExEndDate) - $( ExStartDate) + 1); //-------------------------------------------------- // INTERVAL MATCH JOIN the month records to the calendar // table //-------------------------------------------------- LEFT JOIN (ExchangeRateCalendar) INTERVALMATCH (ExchangeRateDate) LOAD RateStartDate, RateEndDate RESIDENT ExchangeRate; LEFT JOIN (ExchangeRateCalendar) LOAD * RESIDENT ExchangeRate; DROP TABLE ExchangeRate; ExchangeRate: LOAD FromCurrency, ExchangeRateDate, ExchangeRate RESIDENT ExchangeRateCalendar; DROP TABLE ExchangeRateCalendar; - Again create a Table object on the sheet and get all the dimensions from the
ExchangeRatetable. - We will have exchange rates for each of the missing dates as well as shown in the following screenshot:
The main purpose of creating this exchange rate calendar is to tag the exchange rates to every missing date in the range.
The initial data only comes with the rate start dates. So we create a rate end date for each exchange rate using the Peek() function. The Peek() function checks for the last read record for FromCurrency and if it matches, it generates a rate end date of current RateStartDate -1. If FromCurrency doesn't match, then the rate end date is set to today's date.
Using these start and end dates, the calendar is generated.
The exchange rate calendar generated in the preceding recipe can be set for a daily update and stored in a QVD file that can then be used in any Qlik Sense application involving monetary analysis.