topic Re: Slowly Changing dimension in QlikView

Slowly Changing dimension

shiveshsingh — Wed, 25 Nov 2020 16:16:04 GMT

I am having one Brand X which is associated with Store Dimension A

Now suppose, Brand X association with Store Dimension is changed to B today. I don't have any date column in DB

How to cope up with this using SCD concept so that historical data is not altered.

Re: Slowly Changing dimension

sunny_talwar — Wed, 07 Mar 2018 13:18:02 GMT

Create qvds with date stamps so that although you don't have date in the main database, you can stamp them with date (pull date) when you store them into qvd

Re: Slowly Changing dimension

YoussefBelloum — Wed, 07 Mar 2018 13:19:44 GMT

Hi,

Maybe now you need to create a change_date_field to flag this and insert a date on every row until the next change..

Re: Slowly Changing dimension

cristian_av — Wed, 07 Mar 2018 13:56:19 GMT

When you get the data from the database, add a field with current date.

Something like this:

//(You need a script to create the StoredDate.qvd file for the first load)

Concatenate(StoredData) Load * from StoredDate.qvd

Load *

DimensionA,

today(0) as LastUpdate;

From Database;

//In case you need only the last update for each records, use this:

Load MaxString(DimensionA), Max(LastUpdate)

RESIDENT StoredData

GROUP BY DimensionA,LastUpdate;

Be careful that your date will only be loaded when you reload the qvw, so if you reload it every two days, you will have the data every two days and not the real change date.

Re: Slowly Changing dimension

shiveshsingh — Wed, 07 Mar 2018 16:54:54 GMT

Please can you share sample code

Re: Slowly Changing dimension

shiveshsingh — Wed, 07 Mar 2018 16:55:02 GMT

Please can you share sample code

Re: Slowly Changing dimension

sunny_talwar — Wed, 07 Mar 2018 16:56:53 GMT

Share some data

Re: Slowly Changing dimension

shiveshsingh — Thu, 08 Mar 2018 05:54:50 GMT

Haha..:P

i tried and got it

Thanks bhai

Re: Slowly Changing dimension

sunny_talwar — Thu, 08 Mar 2018 10:04:45 GMT

Super