Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
mbespartochnyy
Contributor

How to Link a Date to a Range of Dates

I'm working on exchange rate functionality that will convert values in a table based on date specified on each line of data. I need to link my data table to exchange rate table on CURRENCY and DATE fields. The issue that I'm facing is that my exchange rate table contains a date range instead of a single date. More specifically, I have an exchange rate for a currency with effective date of say April 1, 2016 and an expiration date of April 30, 2016. Other currencies have effective date of January 1, 2016 and an expiration date of December 31, 2016.

Does anyone have any idea of how to link a Fact table to Exchange Rates table on currency and date when date within Fact table is a single point while dates within Exchange Rates table are ranges? Attached is an example. Any help is much appreciated.

Mikhail B.

Tags (1)
2 Replies
MVP
MVP

Re: How to Link a Date to a Range of Dates

You can use INTERVALMATCH LOAD prefix with Currency as key to create a link table between your fact date and the currency validity date range / exchange rate for that fact date.

Then you can JOIN the exchange rate to your fact table.

It's all explained in detail here:

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

martyn_birzys
Contributor

Re: How to Link a Date to a Range of Dates

I had a similar problem, hope it might help you:

Re: Link date range to monthly period