topic How to do slowly changing dimension? in QlikView

How to do slowly changing dimension?

Tue, 25 Sep 2012 07:38:10 GMT

Hi,

Can you help how to do slowly changing dimension?

As of 8/1/2012

Name Status

Bill Single

As of 8/6/2012

Name Status

Bill Married

Please, I want to have simple example. Need help, thanks.

Best Regards,

Bill

Re: How to do slowly changing dimension?

Tue, 25 Sep 2012 07:52:08 GMT

You can add a column "StartDate" and "EndDate".

See this example:

Name Status StartDate EndDate

Bill Single 01/01/2001 08/01/2012

Then when the status is updated on 08/01/2012

Name Status StartDate EndDate

Bill Married 08/01/2012 99/99/9999

The second data means it is the effective status based on 08/06/2012 date. Your query should filter the EndDate = 99/99/9999 to determine the active status. This is how your table looks like.

Re: How to do slowly changing dimension?

Tue, 25 Sep 2012 07:55:11 GMT

Do you have example application?

Re: How to do slowly changing dimension?

Tue, 25 Sep 2012 08:53:35 GMT

Hi,

Use add keyword.

( because of company policy, can't upload qvw )

******************************************** below script pasted *****************************************

Person:

LOAD id as person_id,

name,

date(DOJ) as date_of_joining

FROM SCD.xlsx

(ooxml, embedded labels, table is Person);

Martial_Status:

LOAD person_id,

martial_status,

timestamp(DOM) as ms_modified_on

FROM SCD.xlsx

(ooxml, embedded labels, table is [Martial Status]);

Place:

add LOAD person_id,

place,

timestamp(DOM) as pl_modified_on

FROM SCD.xlsx

(ooxml, embedded labels, table is Place);

*************************************************************************************************************

check there is add keyword used before load in Place table, this will load all those records which are already in store but the value is different in source table.

This will require some date time field in case of database to qvd. For qvd to final dashboard it's ok.

Regards,

Shubhu

Re: How to do slowly changing dimension?

Tue, 25 Sep 2012 21:43:59 GMT

Hi Shubhasheesh,

Will try that. I have additional question, is it really necessary having history table to trace back everything? What I though is that, qlikview itself will track the changes without existing history table. Anyway, will try any possibilities upon.

Thanks,

Bill

Re: How to do slowly changing dimension?

farolito20 — Tue, 11 Dec 2012 21:28:16 GMT

Do you know if QV works without a history table?

Re: How to do slowly changing dimension?

Fri, 04 Jan 2013 01:04:40 GMT

It is in need to have history table.