Script for SCD-2 concept

[UserID2626]

hi,

i need a script based on SCD-2.

i kept an sample table

EMP_ID	EMP_NAME	WORK_DEPARTMENT	JOIN_DATE
1	Preethi	HR	26/02/2015
2	Kavi	Delivary Manager	25/12/2011
3	kiran	Engineer	13/11/2014

Now, considering that Kiran was get promated as senior engineer.

I need to keep the history of employees like below

EMP_ID	EMP_NAME	WORK_DEPARTMENT	Start_DATE	End _date
1	Preethi	HR	26/02/2015	Current date
2	Kavi	Delivary Manager	25/12/2011	Current Date
3	kiran	Engineer	13/11/2014	12/07/2018
3	Kiran	Senior engineer	13/07/2018`	Current Date

i got the concept that need to use peek function, need compare with previous, but got stuck in how to write script for inserting new record next it.

Please help to find the solution.

Thanks in Advance

[consultant_bi]

Hello vivethakumar‌

you need to wright the script of data importation from source table then store it on a qvd file,
table_source:
load *;
sql select * from table;

store 'lib:\\...qvd';

now load the first time to generate the data on qvd from script above; after that change script to the code below:

table_source:
load *;
sql select * from table;

map_table:

mapping load id,department resident table_source;

table_source:

load id,..,

if(end_date='current_date', makedate(year(today()),num(month(today())),day(today()))&'') as end_date

FROM [lib://Source....qvd](qvd)

where department <> applymap('map_table',id)   ;

store 'lib:\\...qvd';

then reload data from the source table which gonna contains the modified line and load from your qvd file the old stored data which contains the previous value of this changed line and make a test to load from it just the lines that had changed on department column, of course by the id of the employee, and on columns called by load make an if statement on end_date field to change the value of current date by the actual date.

[balabhaskarqlik]

Try these:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

================================================

Let ThisExecTime = Now( );

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then

STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

[UserID2626]

this is updating the old records, i need to maintain history, kindly check both tables.

Thanks Fathi

[miskinmaz]

Hi

Hope u find the below code helpful

old_records:

Load * Inline [

EMP_ID,EMP_NAME,WORK_DEPARTMENT,JOIN_DATE

1,Preethi,HR,26/02/2015

2,Kavi,Delivary Manager,25/12/2011

3,Kiran,Engineer,13/11/2014

];

Join

new_records:

Load * Inline [

EMP_ID,EMP_NAME,WORK_DEPARTMENT,JOIN_DATE

3,Kiran,Senior engineer,13/07/2018

4,Sam,Senior engineer,13/07/2018

];

NoConcatenate

temp_table:

LOad

EMP_ID,EMP_NAME,WORK_DEPARTMENT,date(date#(JOIN_DATE,'DD/MM/YYYY')) as JOIN_DATE

Resident old_records;

NoConcatenate

Final_table:

Load *,

if(EMP_ID=Peek(EMP_ID) ,date(peek(JOIN_DATE)-1),date(today()))

    as End_Date

Resident temp_table

order By EMP_ID,JOIN_DATE desc

;

drop Table old_records,temp_table;

Please keep in mind that i have used inline table. In your case resident loads and generation of history qvd will change.

But it will help to implement SCD scenario...

Regards

Miskin

[consultant_bi]

Hello

No it maintains the history and add the date of end date but for the old ones it kept as it is, look for the record of Kiran Employee

[miskinmaz]

Hi,

Updating the if condition is helping to resolve it...

if(EMP_ID=Peek(EMP_ID) ,date(peek(JOIN_DATE,-1)-1),date(today()))

    as End_Date.

Thanks

[UserID2626]

please share your app.

it helps me to understand better

[consultant_bi]

At first write this and load data

table1:

load * inline

[emp_id,emp_name,work_department,start_date,end_date

1,Preethi,HR,26/02/2015,current_date

2,Kavi,Delivary_Manager,25/12/2011,current_date

3,kiran,developper,13/11/2012,current_date

];

store table1 into 'lib://Source (qlik-sense_administrateur)/tab.qvd' ;

then after your first load change the script to this :

table1:

load * inline

[emp_id,emp_name,work_department,start_date,end_date

1,Preethi,HR,26/02/2015,current_date

2,Kavi,Delivary_Manager,25/12/2011,current_date

3,kiran,Engineer,13/11/2015,current_date

];

map_load:

mapping load emp_id,work_department resident table1;

table1:  

load

emp_id,emp_name,work_department,start_date,

if(end_date='current_date', makedate(year(today()),num(month(today())),day(today()))&'',end_date) as end_date

FROM [lib://Source (qlik-sense_administrateur)/tab.qvd](qvd)

where work_department <> applymap('map_load',emp_id)   ;

store table1 into 'lib://Source (qlik-sense_administrateur)/tab.qvd' ;