How to do slowly changing dimension?

[#1]

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

[#2]

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.

[#3]

Do you have example application?

[#4]

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

[#5]

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

[farolito20]

Do you know if QV works without a history table?

[#7]

It is in need to have history table.