Slowly Changing Dimensions

PresidReporting — Sat, 16 Nov 2024 04:37:11 GMT

Hi,

I need to build out a dashboard that utilizes incremental loads and slowly changing dimensions. I understand the idea behind SCD, and I believe I need type 2.

We have a main app that pulls info from various SQL sources. At the end of that loadscript, I store five fields into a qvd. I need to now build a new app that loads this stored qvd plus any NEW data. See below. (UserRoles.qvd is the stored QVD).

OLD:
load
UserID,
Critical,
Role,
SubRoleName,
reloadtime() as CreateDate

FROM
[C:\QVDev\SourceDocs\Shared QVD\Dimensional\UserRoles.qvd]
(qvd);

Concatenate
NEW:
LOAD UserID,
Role,
Critical,
SubRoleName,
Reloadtime() as CreateDate

FROM
[C:\QVDev\SourceDocs\QVW\Application\myOrders Resources\RoleChange.xlsx]
(ooxml, embedded labels, table is Change);

Obviously this does not work the way I want. If there is an updated/changed record in the NEW table, I want the OLD table to have a new column called 'EndDate' with the date that the updated record came in. I want the NEW record to now contain a populated 'CreateDate' field. I want to keep both records.

Thoughts?

topic Slowly Changing Dimensions in QlikView

Slowly Changing Dimensions