topic Re: Slowly changing dimension in QlikView

Slowly changing dimension

jvitantonio — Fri, 16 Nov 2012 20:17:11 GMT

Hi all,

I have a really complex model where I need to use slowly changing dimensions. I have a Dimension table, that use the same code but different description depening on dates. Ex: From Jan-Mar the description for code 44444 is "Desc1" and for Apr-Jun the description for the same code is "Desc2".

Now, I join this table to my fact and want to make different analysis depending on the date of my fact table and of couse I should show the correct description according to the date. I did a simple example and it work, but I cannot get it work with complex date selections and would like to know your opinion. I attach a really simple example that pictures my situation and the type of analysis I need to do.

Thanks and I hope you can help me out.

Re: Slowly changing dimension

Gysbert_Wassenaar — Fri, 16 Nov 2012 20:34:24 GMT

Try using the intervalmatch function:

Dimension:

LOAD

Code,

date(Date_From, 'DD/MM/YYYY') AS Date_From,

date(Date_To, 'DD/MM/YYYY') as Date_To, Description

INLINE [

Code, Date_From, Date_To, Description

A, 01/01/2012, 28/02/2012, CodeXX

A, 01/03/2012, 31/05/2012, CodeYY

];

Fact:

LOAD

Code, date(Date,'DD/MM/YYYY') AS Date, Value

INLINE [

Code, Date, Value

A, 01/01/2012, 10

A, 03/01/2012, 20

A, 03/03/2012, 15

A, 01/05/2012, 70

];

Left join (Fact)

IntervalMatch(Date)

load Date_From, Date_To

Resident Dimension;

left join(Fact)

load *

Resident Dimension;

Drop Table Dimension;

Re: Slowly changing dimension

jvitantonio — Fri, 16 Nov 2012 21:35:23 GMT

Hi G, thanks for you time. I should have mentioned this in my first post but interval match is not an option here. This need to be done within the graphics/tables. Your solution would work if in my dimension table I use different codes. So you match each code depending on the dates. In my model I have the same code with different description.

Also in your example you drop the dimension table. I cannot do this because I also manage multilanguage records, so I need to have the tables separte otherwise my fact table becomes really dirty.

Do you have any other idea?

Thank you for you time and hope to hear from you soon.

Re: Slowly changing dimension

Gysbert_Wassenaar — Sat, 17 Nov 2012 08:39:57 GMT

I think you're wrong about some things. First of all if you have to deal with slowly changing dimension using intervalmatch is pretty much the only sensible approach afaik. Unless you want to do your ETL in another tool (which will probably do the same, but in a way you don't see it). I also think you may be a little too much wedded to the fact - dimension table divide. It doesn't always make sense to strictly adher to that divide in Qlikview. However, if you think you must keep your fact table 'clean' have a look at the attached qvw.

Re: Slowly changing dimension

jvitantonio — Sat, 17 Nov 2012 09:21:47 GMT

Thank you G! I will need some time to analyse this and will get back to you ASAP.