incremental load

[Anonymous]

Hi all

i have a question regarding incremental load

the table looks like this:

first name

lastname

department

when there is a new person it should be append to the table.

when there is a change in the department I want to add a new line.

also if possibke I want to add the columns validfrom (first time load)

and validto (if there is no change it should be today, whne there is a change it should be the date of the change)

do u have expirience in this or a reference where I can read about this ?

‌

thanks in advance!

[maxgro]

I think you need a slowly changing dimension

https://community.qlik.com/search.jspa?q=slowly+changing+dimension&type=document

[uacg0009]

Hi,

There is a question before I can give the solution. In you source, if there is a person change the department, will it add a new column or it update the column?

Thanks.

Aiolos

[Anonymous]

Thanks for ur reply.

Maybe I have to specify my problem:

I have the first last name and the department. When an employee moves to a new department the old one will be overwritten.

[uacg0009]

Hi,

I think you need a linked_key to do this. For example, assuming you already have the history data, and you want to update.

T1:

Load Linked_Key,

         Linked_Key_Only_Name,

         firstname,

         lastname,

         department,

         validfrom,

         validto

From YourHistoryData;

T2:

Noconcatenate

Load text(firstname & lastname & department) as Linked_Key_New,

         text(firstname & lastname) as Linked_Key_Only_Name_New,

         firstname,

         lastname,

         department,

         today() as validto

From YourSource

Where not exist(Linked_Key,text(firstname & lastname & department));

left join

Load Linked_Key_Only_Name as Linked_Key_Only_Name_New,

         validfrom

From YourHistoryData

Where exist(Linked_Key_Only_Name_New,Linked_Key_Only_Name);

T3:

Noconcatenate

Load Linked_Key as Linked_Key_Update,

         Linked_Key_Only_Name as Linked_Key_Only_Name_Update,

         firstname,

         lastname,

         department,

         validfrom,

         today() as validto

From YourHistoryData

Where exist(Linked_Key_Only_Name_New,Linked_Key_Only_Name);

Final:

Noconcatenate

Load Linked_Key,

         Linked_Key_Only_Name,

         firstname,

         lastname,

         department,

         validfrom,

         validto

Resident T1

Where not exists(Linked_Key_Only_Name_Update,Linked_Key_Only_Name);

Concatenate

Load Linked_Key_Update as Linked_Key,

         Linked_Key_Only_Name_Update as Linked_Key_Only_Name,

         firstname,

         lastname,

         department,

         validfrom,

         validto

Resident T3;

Concatenate

Load Linked_Key_New as Linked_Key,

         Linked_Key_Only_Name_New as Linked_Key_Only_Name,

         firstname,

         lastname,

         department,

         if(isnull(validfrom),today(),validfrom) as validfrom,

         validto

Resident T2;

Drop tables T1,T2,T3;

Please try this.

Thanks.

Aiolos Zhao