Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
zekazak
Contributor

How to Create Slowly Changing Dimension

Hello,

I've got a problem of creating slowly changing dimension in qv. So I've got the source file with product_segments. And each product segment should have scd_start and scd_end dates. Initially these dates are predefined by the business people, but sometimes the names of the product segments are being changed and I have to log this change in my qvd. How can I get this done in the loadscript?

I've attached the file with an example of what I'm trying to achieve.

Any help would be much appriciated. Thank you in advance.

~Sergejs

3 Replies
Employee
Employee

Re: How to Create Slowly Changing Dimension

This is not very difficult, but it takes some scripting.

First of all, you need to save the historical records - the records that tell you which Product Segments a Product used to belong to.

Then you need to load both your dimension table (in your case, the products with the validity intervals) and the transaction table (sales, inventory, etc.). Finally, you need to link the two tables using a table with exactly two keys: Product + Date (linking to the transaction table) and Product + Validity interval (linking to the dimension table). This"bridge table" can be created using a while loop (see blog post about looping over intervals)

My example below is about Salespeople changing regions/departments, but it is essentially the same problem.

HIC

Image1.png

zekazak
Contributor

Re: How to Create Slowly Changing Dimension

Hi Henric,

This is very useful post and I'll definitely use this logic at the second stage. Sorry that was my fault I haven't explained the problem properly.

I've got a source file without scd_start and scd_end fields. So I have to set them while I'm loading the source file first time and then update scd_start and scd_end dates in future reloads in case if there were changes in the source file. So I've been trying to find any methods on web but with no result. If I could get any idea that would awesome.

Best Regards,

~Sergejs

zekazak
Contributor

Re: How to Create Slowly Changing Dimension

Hi Henric,

Now I'm on this stage where COGS_X_DATES has exactly one record per combination of _Key(PIG&PRODUCT_SEGMENT) and Date.

currsch1.png

What is the best way to link the SALES table to this schema If I want to see right COGS values at the time when order was made? And I don't realy understand how to build this Bridge table.

SALES table contain the fields like:

_DateKey,OrderID, ProductID, Product Segment, PIG

and have mltiple lines per orderid.

This is the code I'm using to create the schema above:

COGS_HISTORY:

LOAD

           upper(replace(PIG&PRODUCT_SEGMENT,' ','')) as _Key,

           PIG as H_PIG,

     PRODUCT_SEGMENT as H_PRODUCT_SEGMENT,

     round(TOTAL_VARIABLE,0.0001) as H_TOTAL_VARIABLE,

     round(TOTAL_FIXED,0.0001) as H_TOTAL_FIXED,

     round(FULL_UNIT_COST,0.0001) as H_FULL_UNIT_COST,

     num(date(VALID_FROM,'DD/MM/YYYY')) as H_VALID_FROM,

     num(date(VALID_TO,'DD/MM/YYYY')) as H_VALID_TO

   

FROM

D:\Qlikview\DEV\NA\CM\QVW\QVW_SourceFolder\Business\Cogs\Source_For_QV_Schema_v1.xlsx

(ooxml, embedded labels, table is COGS);

   

COGS_X_DATES:

Load _Key

    //,UPPER(REPLACE(_Key&num#(date(H_VALID_FROM+ IterNo()-1,'YYYYMMDD')),' ','')) as _KeySalesRef

          ,H_PIG as PIG

          ,H_PRODUCT_SEGMENT as PRODUCT_SEGMENT

          ,date(H_VALID_FROM + IterNo()-1) as Date

          ,num#(date(H_VALID_FROM+ IterNo()-1,'YYYYMMDD')) as _CogsDateKey

          ,H_FULL_UNIT_COST as FULL_UNIT_COST

           ,H_TOTAL_VARIABLE as TOTAL_VARIABL

          ,H_TOTAL_FIXED as TOTAL_FIXED

          ,H_VALID_FROM as VALID_FROM

          ,H_VALID_TO as VALID_TO

          ,num#(date(H_VALID_FROM,'YYYYMMDD')) as VALID_FROM_KEY

          ,num#(date(H_VALID_TO,'YYYYMMDD')) as VALID_TO_KEY

Resident COGS_HISTORY

While IterNo() <= H_VALID_TO - H_VALID_FROM + 1

;

Can you suggest anything about this?

Thank you very much in advance.

~Sergejs

Community Browser