Incremental Load Insert & Update for SCD2

[#1]

Hi Experts,

We are implementing Incremental loads for Dimension tables and need some immediate help here. I have 3 customers with st_dt, EndDate

Lets say customerid 1 Place information is changed from Hyderabad to 'Singapore' and the EndDate for this record is updated and a new record is inserted (Slowly changing dimension 2 ) as shown below.

I am sure that we can get the updated record for Cust1 (st_dt=12/12/2015 and EndDate=31/12/9999) using st_dt>max(Date). But my question is how to update the  EndDate from 31/12/9999 to 11/12/2015.

Can any one help me out how to get the updated records for a customer with the updated information.??

Thank you

[Gysbert_Wassenaar]

Dimension tables are usually not large enough to make incremental loading a necessity. So why not load only the records with the EndDate 31/12/9999?

MyDimension:

LOAD *

FROM MySource

WHERE EndDate = '31/12/9999';

If you need all the records and want to get only the changed and new records then you need either a mutation date in addition to the fields you already have. If you don't have that then you have to create a hash of all the fields and use that to check if a records has changed. See these blog posts for more information:

QlikView hash functions and collisions - The Qlik Fix! The Qlik Fix!‌

http://www.quickintelligence.co.uk/qlikview-incremental-load/

[reddy-s]

Hi Ramesh,

This will help you as well.

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Best Regards,

Sangram Reddy.

[#4]

Hi Sangram..Thank you..But this will help me for getting the updated records alone. How about the records those are changed with end dates as I mentioned in my example. My concern is to get the latest record and also at the same time the existing record end date should be updated with the modified end date which is actually 9999 date in the qvd .

[#5]

Hi Gusbert,

Thank you.

But this results in giving the updated record with 9999 date and also for the history records in QVD instead of 11/12/2015 as mentioned above. Please suggest me how to get the modified EndDate from the database as its actually stored as 31/12/9999 with the previous run.

[reddy-s]

Hi Ramesh,

Try this:

After you fetch the data order it by customer ID and Start date (ascending):

[Data]

LOAD *

FROM MySource

order by CustomerID st_dt;

[temp]:

load CustomerID,

       Name,

       State,

       st_dt,

    if(peek('CustomerID', (rowno() + 1), 'Data') = CustomerID, // checking with the next row's customer id

    if(peek('EndDate', (rowno() + 1), 'Data') = '31/12/9999',today(),EndDate))//change the date to today based on Enddate

      resident [Data];