8 Replies Latest reply: Jul 16, 2018 6:59 AM by Fathi Ali RSS

    Script for SCD-2 concept

    Vivetha Kumar

      hi,

      i need a script based on SCD-2.

      i kept an sample table

      EMP_IDEMP_NAMEWORK_DEPARTMENTJOIN_DATE
      1PreethiHR26/02/2015
      2KaviDelivary Manager25/12/2011
      3kiranEngineer13/11/2014

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

      I need to keep the history of employees like below

      EMP_IDEMP_NAMEWORK_DEPARTMENTStart_DATEEnd _date
      1PreethiHR26/02/2015Current date
      2KaviDelivary Manager25/12/2011Current Date
      3kiranEngineer13/11/201412/07/2018
      3KiranSenior engineer13/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

        • Re: Script for SCD-2 concept
          Fathi Ali

          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.

            • Re: Script for SCD-2 concept
              Vivetha Kumar

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

               

              Thanks Fathi

                • Re: Script for SCD-2 concept
                  Fathi Ali

                  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

                   

                  dd.png

                    • Re: Script for SCD-2 concept
                      Miskin Mazgaonkar

                      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.

                       

                      Capture.JPG

                       

                      Thanks

                      • Re: Script for SCD-2 concept
                        Vivetha Kumar

                        please share your app.

                        it helps me to understand better

                          • Re: Script for SCD-2 concept
                            Fathi Ali

                            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' ;

                    • Re: Script for SCD-2 concept
                      Bala Bhaskar

                      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

                      • Re: Script for SCD-2 concept
                        Miskin Mazgaonkar

                        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