QlikView documentation and resources.
These questions and others are answered in this Technical Brief.
Thank you Barry for good discussions.
See also the following blog posts
Creating a Date Interval from a Single Date
Creating Reference Dates for Intervals
Slowly Changing Dimensions
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.
Thanks & Regards,
Are there any Layout and Visualization examples using a Slowly Changing Dimension Data Model to specifically show what changes have occurred and the impact?
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.
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?
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,
In the Data Generate.qvs file you have an error on line 75
Autogenerate $(vNumberOfTransactions); should be Autogenerate $(vNumberOfTrans);
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
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.
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
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;
Load *, quantity * price as value Resident Transactions;
Drop Table BridgeTable, PriceTable, Transactions;