article IntervalMatch and Slowly Changing Dimensions in Qlik Analytics in Official Support Articles

IntervalMatch and Slowly Changing Dimensions in Qlik Analytics

hic — Mon, 11 May 2026 08:24:48 GMT

What techniques are there to match a number with an interval?
How does the IntervalMatch prefix work?
Should I join the interval match table with the event table?
How do I generate an interval from a single change date?
What is a Slowly Changing Dimension?
How do I model a multi-level Slowly Changing Dimension?

These questions and others are answered in the attached Technical Brief.

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Fri, 16 Aug 2013 20:54:23 GMT

Fantastic job on this document. Thank you.

I have a task I wish to accomplish within a Qlikview application which looks almost exactly like what this gentleman has described solving in SQL Server:

http://www.sqltreeo.com/wp/how-to-merge-combine-date-intervals-in-t-sql/

It would be like collapsing multiple dimension intervals into one unified table. Not sure if this makes sense but the example above shows exactly what I'd like to do. I can't picture how to code this in Qlikview.

Thanks,

Re: IntervalMatch and Slowly Changing Dimensions

hic — Tue, 20 Aug 2013 14:13:06 GMT

Isn't this the same as the partitioning I do on page 17?

HIC

Re: IntervalMatch and Slowly Changing Dimensions

pgrenier — Tue, 20 Aug 2013 15:21:08 GMT

Excellent work! This is a well written and very clear presentation of a precious QlikView functionality that is so poorly documented in the QlikView Help (F1)!

Thanks! 🙂

Re: IntervalMatch and Slowly Changing Dimensions

phaneendra_kunc — Tue, 20 Aug 2013 16:54:11 GMT

Excellent Work !!!

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Wed, 21 Aug 2013 17:29:10 GMT

Exactly!

My apology. I realized I did not read the last section of your doc which discusses exactly what I was asking.

Re: IntervalMatch and Slowly Changing Dimensions

Fri, 23 Aug 2013 13:42:12 GMT

Very well explained, well writen post with clear and good examples in the .zip file.

This really helped me a lot to understand how to use intervalmatch!

Thank you.

Re: IntervalMatch and Slowly Changing Dimensions

Wed, 05 Feb 2014 05:57:24 GMT

Comprehensive,Precise and absolutely stunning post.Must read for every Qlikview Developer. Thanks Henric.

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Fri, 23 May 2014 13:29:59 GMT

Hi Henric,

Could you please let me know how to open this QVS files which you have attached in example?

Thanks & Regards,

Kiran Kokade

Re: IntervalMatch and Slowly Changing Dimensions

hic — Fri, 23 May 2014 13:38:40 GMT

The qvs (QlikView Script) files are plain text files that you can open with any text editor, e.g. Notepad.

Or, you can open the QlikView Script editor and do "Insert -> Script File"

HIC

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Fri, 23 May 2014 15:28:29 GMT

Sorry Henric

But how should I run this example.It consist QVS file.

When I am trying to inser using "Insert -> Script File" wizard,It is creating circular loop.

Thanks & Regards

Kiran Kokade

Re: IntervalMatch and Slowly Changing Dimensions

hic — Fri, 23 May 2014 15:52:28 GMT

Then you have probably used more than one qvs file. Just insert one file into the script.

If you extract all files to one folder, you will get a folder structure. Each subfolder contains some data and several qvs files. A qvs file typically contains one way to load the data and the next qvs contains another way to load the data. So use them one by one.

See also the pdf document where the different methods are described.

Good Luck!

HIC

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Fri, 23 May 2014 15:57:53 GMT

Thanks Henric.

Thanks & Regards,

Kiran Kokade

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Mon, 02 Jun 2014 20:55:09 GMT

Are there any Layout and Visualization examples using a Slowly Changing Dimension Data Model to specifically show what changes have occurred and the impact?

Re: IntervalMatch and Slowly Changing Dimensions

Tue, 01 Jul 2014 13:12:33 GMT

Very productive technical brief, thanks Henric Cronström!

I wonder how to manage a situation with two-level slowly changing dimension, but on the second level are two interval tables dependent on the first level.

Example:

First level: Profit Center

Second level: Cost center, Order

I think it's a similar approach to the Two-level SCD, but what to do with the Where-Clause... in the SubInterval?

Re: IntervalMatch and Slowly Changing Dimensions

Fri, 18 Jul 2014 19:04:50 GMT

Henric,

Your article and examples have helped me out a lot. However, the data model I'm dealing with is a little more complex. Say I have 3 Tables (All with intervals) that I've successfully created sub intervals similar to your incidents and shifts examples. From my understanding the new sub intervals are closed, non overlapping intervals. With this same data model, I can't seem to figure out how to add an additional table that has intervals but they have nothing do with the previous 3 tables. That is, I want this 4th table to have the original intervals with end points included in the intervals. Is it possible to achieve this within the current data model to allow only 1 calendar be used? Ultimately what I'm trying to accomplish for the 4th table is that when I select a date from the calendar in which is connected via intervalmatch, I want the 4th table to have the date appear in both the subintervalbegin AND the subintervalend (overlapping). So if my date selection is 7/14/2014, that date should appear in both the subintervalbegin and subintervalend list box or field for this 4th table. Perhaps this 4th table is added to the data model differently...

The image below shows 7/14/2014 as the Date. I'm trying to get it to appear in both subintervalbegin and subintervalend for the 4th table.

Thanks for the assistance,

Ethan

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Mon, 02 Mar 2015 20:08:09 GMT

In the Data Generate.qvs file you have an error on line 75

Autogenerate $(vNumberOfTransactions); should be Autogenerate $(vNumberOfTrans);

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Mon, 17 Aug 2015 22:22:01 GMT

Henric,

How would we take it a step further? In your Shifts example if we were to say add in an employeeID. So we would have to break out for each employee, their shift, machines (multiple for the shift), machine duration.

I have a similar circumstance to solve for.

Each customer has a plan and during the time on a particular plan they have features. The difference here is Features can go overlap one Plan to the next as well as have multiple Features at any one time.

Thank you for your help and all the work you do in the community.

Data for the above

CustomerKey	SKU	fStartDt	fEndDt	Type
656289	SIMP14	3/5/2010	3/7/2010	Plans
656289	SIMP29	3/8/2010	7/7/2011	Plans
656289	SIMPDOMUNL79	7/8/2011	9/8/2011	Plans
656289	PREM39	9/8/2011	12/8/2013	Plans
656289	GC506A	12/8/2013	8/8/2014	Plans
656289	GC505A	8/8/2014	9/30/2015	Plans
656289	JCW2	3/10/2010	9/30/2015	Features
656289	VMS2	7/9/2011	9/8/2013	Features
656289	GC318Z-02	6/27/2015	9/30/2015	Features
656289	GC1000Z-02	6/27/2015	9/30/2015	Features
815869	SIMP19	6/3/2012	2/3/2013	Plans
815869	GC505A	2/3/2013	2/3/2013	Plans
815869	GC506A	2/3/2013	3/3/2013	Plans
815869	GC507A	3/3/2013	5/3/2013	Plans
815869	GC505A	5/3/2013	1/3/2014	Plans
815869	GC506A	1/3/2014	4/3/2014	Plans
815869	GC505A	4/3/2014	1/3/2015	Plans
815869	GC506A	1/3/2015	5/3/2015	Plans
815869	GC524B	5/3/2015	9/30/2015	Plans
815869	VMM2	3/11/2011	6/3/2012	Features
815869	FDD2	7/20/2011	6/17/2012	Features
815869	VMM2	6/3/2012	9/30/2015	Features
815869	FDD2	6/17/2012	6/17/2012	Features
815869	GC304A-02	11/28/2012	5/5/2013	Features

Re: IntervalMatch and Slowly Changing Dimensions

Anonymous — Wed, 02 Sep 2015 16:51:13 GMT

Just to follow up.

I was able to solve this using the 2 Level Slowly Changing Dimension information.

Thank you Henric for putting that all together.

Re: IntervalMatch and Slowly Changing Dimensions

mikefromoke — Mon, 25 Jan 2016 08:32:27 GMT

Hello Henric,

thank you for sharing this brief. It gives me the right way, to understand "interval match" abd "SCD".

but there is one open question for me...

in my example, i also have a transaction table (transactiondate , itemno, quantitiy) and a pricetable(fromdate, todate, price, itemno). i want to find the right price, relating to the transactiondate, and also want to calculate a value with "quantitiy * price" in the transactiontable.

so, i can find the right price (as SCD) but, how can i calculate a value? the correct price isn't a field in the transaction table yet....

thanks for an information

regards

mike