Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
this is updating the old records, i need to maintain history, kindly check both tables.
Thanks Fathi
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
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
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
please share your app.
it helps me to understand better
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' ;