topic Re: incremental load in QlikView

incremental load

Anonymous — Thu, 21 Dec 2017 06:51:41 GMT

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!

Re: incremental load

maxgro — Thu, 21 Dec 2017 17:59:40 GMT

I think you need a slowly changing dimension

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

Re: incremental load

uacg0009 — Fri, 22 Dec 2017 03:11:53 GMT

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

Re: incremental load

Anonymous — Fri, 22 Dec 2017 09:28:45 GMT

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.

Re: incremental load

uacg0009 — Mon, 25 Dec 2017 07:27:17 GMT

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