Qlik Community

Ask a Question

Documents

QlikView documentation and resources.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE

IntervalMatch and Slowly Changing Dimensions

Henric_Cronström

IntervalMatch and Slowly Changing Dimensions

  • 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 this Technical Brief.

Thank you Barry for good discussions.

See also the following blog posts

IntervalMatch

Creating a Date Interval from a Single Date

Creating Reference Dates for Intervals

Slowly Changing Dimensions

HIC


Attachments
Comments
Henric_Cronström

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

0 Likes
kirankkk
Creator II
Creator II

Thanks Henric.

Thanks & Regards,

Kiran Kokade

0 Likes
michael_gardner
Creator III
Creator III

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

0 Likes
Not applicable

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?

0 Likes
Not applicable

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

CalendarSelection.PNG.png

0 Likes
michael_gardner
Creator III
Creator III

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

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

0 Likes
darrin_pilkingt
Creator II
Creator II

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.

Plan Interval.jpg

Data for the above

     

CustomerKeySKUfStartDtfEndDtType
656289SIMP143/5/20103/7/2010Plans
656289SIMP293/8/20107/7/2011Plans
656289SIMPDOMUNL797/8/20119/8/2011Plans
656289PREM399/8/201112/8/2013Plans
656289GC506A12/8/20138/8/2014Plans
656289GC505A8/8/20149/30/2015Plans
656289JCW23/10/20109/30/2015Features
656289VMS27/9/20119/8/2013Features
656289GC318Z-026/27/20159/30/2015Features
656289GC1000Z-026/27/20159/30/2015Features
815869SIMP196/3/20122/3/2013Plans
815869GC505A2/3/20132/3/2013Plans
815869GC506A2/3/20133/3/2013Plans
815869GC507A3/3/20135/3/2013Plans
815869GC505A5/3/20131/3/2014Plans
815869GC506A1/3/20144/3/2014Plans
815869GC505A4/3/20141/3/2015Plans
815869GC506A1/3/20155/3/2015Plans
815869GC524B5/3/20159/30/2015Plans
815869VMM23/11/20116/3/2012Features
815869FDD27/20/20116/17/2012Features
815869VMM26/3/20129/30/2015Features
815869FDD26/17/20126/17/2012Features
815869GC304A-0211/28/20125/5/2013Features
0 Likes
darrin_pilkingt
Creator II
Creator II

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.

0 Likes
mikefromoke
Creator II
Creator II

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

0 Likes
Henric_Cronström

There are a couple of ways to do this. If you create your SCD solution with quantity and price in different tables, you can directly use

     Sum( quantity * price )

as measure in your chart. In other words: You don't need to create the value as a field in the transaction table. This expression will work as long as there is only one price for each record in the transaction table.

It can, however, be slow. So for that reason you may still want to create the value as a field. If so, you should first join the SCD tables onto the transaction table, then run a second pass through the transaction table creating the field value. E.g. roughly

...

Left Join (Transactions) Load * Resident BridgeTable;

Left Join (Transactions) Load * Resident PriceTable;

NewTransactions:

Load *, quantity * price as value Resident Transactions;

Drop Table BridgeTable, PriceTable, Transactions;

HIC

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-06-03 07:51 AM
Updated by: