Slowly Changing Dimensions

Submitted by seebach on Wed, 01/26/2011 - 21:59

I was looking at examples of how other Qlik developers have implemented Slowly Changing Dimensions in QlikView. I found some examples, but most contained synthetic keys and did not take account for multiple products.

So I though I would do an improved example, which method easely could implemented in the real world. This can be used both for Type 2 and Type 3.

 

If you're new to Slowly Changing Dimensions have a look at wikipedias article.

I've based the example on the code from "colnr" who posted it in this thread in the qlikcommunity.

See my code below or download my qvw:

DimProductsTmp:
//This is the changing Dimension, the product code
// remains the same however the product name and vendor change
// We need to be aable to Track Sales against 
// each change of product name
LOAD * INLINE [ProductCode,Price,Product, Vendor,Start,End
    510, 100,Product,TopMax,*,02-10-2009  
    490, 40,Model,TopMax,*,02-10-2009
    510, 110,Product Red,WonderWau,03-10-2009, 04-10-2009
    490, 50,Model Red,Jeex,03-10-2009, 04-10-2009
    510, 120,Product,UltraMega,05-10-2009,*
    490, 50,Model Red,Jeex,05-10-2009,*
    ];

FactSalesLines:
//These are the actual Sales transactions
//Each SalesDate looks up against DimProducts
// for the Correct product name and summary level 
LOAD * INLINE [ProductCode, SaleID,SalesLine,SalesDate,SalesValue,
   	510, 1,1, 01-10-2009,100
    510, 2,1, 02-10-2009,100
    510, 3,1, 03-10-2009,100 
    490, 3,2, 03-10-2009,50 
    510, 4,1, 10-10-2009,100 
    490, 4,2, 10-10-2009,40 
    ];
    
// Use interval match to match intervals
JOIN  (DimProductsTmp) INTERVALMATCH (SalesDate,ProductCode) 
LOAD Start,End,ProductCode RESIDENT DimProductsTmp;
// exit script;
// Create new DimProducts thats contains a single 
// key based on the keys we created in the interval match
DimProducts:
NOCONCATENATE LOAD DISTINCT 
Price,SalesDate,Product,ProductCode, Vendor,Start,End,
ProductCode&'-'&Start&'-'&End AS ProductKey,
ProductCode AS ProductCodeOld 
RESIDENT DimProductsTmp;

// Join the new key into FactSales so we 
have a single key between the tables
JOIN (FactSalesLines) LOAD SalesDate,ProductCode,
ProductCode&'-'&Start&'-'&End AS ProductKey RESIDENT DimProducts ;

// remove unwanted keys,ProductCode
DROP FIELDS SalesDate,Start,End,ProductCode FROM DimProducts;
DROP TABLE DimProductsTmp;

GHD

Submitted by Anonymous on Wed, 11/09/2011 - 04:56.

ghd lisseur

styler ghd

ghd fers plats

CHI Flat Iron

CHI Hair

CHI Straightener

ghd planchas

ghd espa?a

ghd baratas

chi hair products

chi hair straightener

GHD

Submitted by Anonymous on Wed, 11/09/2011 - 04:55.

ghd lisseur

styler ghd

ghd fers plats

CHI Flat Iron

CHI Hair

CHI Straightener

ghd planchas

ghd espa?a

ghd baratas

chi hair products

chi hair straightener

I liked it, and I thought

Submitted by Anonymous on Fri, 10/07/2011 - 07:17.

I liked it, and I thought there were a lot of subtleties and nuances that you don’t get right away. But that’s just my opinion, you’re completely entitled to yours.
flower delivery in portugal | send flowers to israel

I liked it, and I thought

Submitted by Anonymous on Fri, 10/07/2011 - 06:01.

I liked it, and I thought there were a lot of subtleties and nuances that you don’t get right away. But that’s just my opinion, you’re completely entitled to yours.portugal florist | israel florist

monster beats headphones

Submitted by Anonymous on Sat, 09/17/2011 - 15:15.

 You cheap monster headphones can cheap Mac makeup wash and towel dry coach handbags on sale his Coach Outlet Store Online hair (and leave it like that) coach outlet online to complete the coach outlet store surfer boy monster beats headphones look mac makeup They Monster Headphones discount supply Monster Headphones the ties wholesale makeup within the stipulated mac cosmetics wholesale time in a cost effective way who mac cosmetics is a good monster dr dre headphones Silk Islamic coach outlet Activist


 

Coach Factory Store

Submitted by Anonymous on Fri, 09/30/2011 - 12:01.

[url=http://www.coachfactory-store.net/]Coach Factory Store[/url]
Moncler Jackets
Moncler Jackets
Moncler Jackets
Moncler Jackets

Do you like mac makeup to

Submitted by Anonymous on Tue, 09/13/2011 - 08:37.

Do you like mac makeup to coach outlet wear monster dr dre headphones casual cheap uggs sale
shoes coach outlet online ? uggs sale Usually how to Coach Outlet Store Online select men's casual shoes?A uggs uk casual shoe has nike zoom kobe v become mac cosmetics wholesale a cheap Mac makeup very popular wholesale makeup shoe coach outlet store of choice This coach handbags on sale is monster beats headphones why zoom kobe 5 baseball Kobe Bryant Shoes pays Monster Headphones its players uggs cheap more nike air max moneyThird


 


 

reply this post

Submitted by Anonymous on Sun, 07/31/2011 - 19:32.

A lot of specialists say that credit loans help a lot of people to live the way they want, because they are able to feel free to buy necessary goods. Moreover, some banks give credit loan for all people.

gucci for sale

Submitted by Anonymous on Sat, 07/30/2011 - 07:03.

ong-term by Vibram Kso the bread to eat, suffering from stomach Five Fingers Shoes FiveFingers Sale ulcers, stomach bleeding occurs. When that patient needs to spend million yuan, she secretly ran the hospital. She put all hopes on the old Classic Fivefingers Wei body. Wei old prescription no fees, but also actively took 50 dollars out of her pharmacy to buy Vibram 5 Finger Shoes med...

respond this topic

Submitted by Anonymous on Sun, 07/10/2011 - 07:06.

Some specialists tell that personal loans help people to live their own way, just because they can feel free to buy needed things. Furthermore, banks present financial loan for all people.

Amazing wedding dresses

Submitted by Anonymous on Fri, 06/17/2011 - 12:54.

What's some thing I could wear under my A Line Wedding Dress to help minimize cleavage?

It truly is the maggie Sottero Dallas Marie dress. It really is a church wedding with a lot of family members and I just don't really feel comfy showing off my cleavage. Please aid....acquiring married in a month and I'm stuck!!

How about getting a piece of fine lace and have it stitched in location to cover your cleavage. You must be able to discover lace to match your dress or veil (if it has a lace). I also believe you need to ask your mother and possibly also a few other women inside your loved ones, if they feel you dress is cut too low. You may be surprised to find that they like your dress just as it really is. Nevertheless, it's of course most important which you feel beautiful and comfy on your wedding day. You might go to boxweddingdress.com which have Amazing A Line Wedding Dresses.

Who can give some suggestions about purchasing A Line Wedding Dress?

I wish to get A Line Wedding Dresses at one place,but Now, I haven't discovered satisfactory wedding dress. Is there any great suggestions for me? You could go to boxweddingdress.com, they sell not merely fashion wedding dresses but some elegant bridesmaid Dresseses and cute flower girl dresses.At the very same time you also locate A Line Wedding Dress for you.Please belive me that's an excellent option.