3 Replies Latest reply: Apr 14, 2015 5:39 AM by Markus Schnabl RSS

    Error correction - missing date in script

      Good morning,

       

      i have a problem with an inconsistent data source of HR data from excel files. Because these files are exported from a third party software there is no chance so far to fix the problem in near future, so i wanted to correct the data during the script load.

      I just dont really know how and hope anyone could give me helpful input.

       

      I get a list of employees every month containing following columns

       

      ActualPeriod, EmployeeID, EntryDate, RetirementDate, EmploymentRelationShip, EmpRelStartDate, EmpRelEndDate ... and several HR data not relevant for this problem

       

      RetirementDate and EmpRelEnddate can be null if the employee has an active employment or no changes with his employment. But under certain conditions the employment type changes and he gets a second record in the data but the "older" record does not have an EmpRelEndDate.

       

      For example:

      LOAD * INLINE

      [Period, EmployeeID, EntryDate, RetirementDate, EmploymentRelationShip, EmpRelStartDate, EmpRelEndDate

      201501, 1, '01.01.2012', , 'apprentice', '01.01.2012',

      201502, 1, '01.01.2012',, 'apprentice', '01.01.2012',

      201502, 1, '01.01.2012',, 'employee', '15.02.2015',

      201503, 1, '01.01.2012',, 'employee', '15.02.2015',

      201501, 2, '01.01.2014',, 'employee', '01.01.2014',

      201502, 2, '01.01.2014',, 'employee', '01.01.2014',

      201503, 2, '01.01.2014',, 'employee', '01.01.2014',

      201501, 3, '01.01.2013',, 'employee', '01.01.2013',

      201502, 3, '01.01.2013','30.04.2015', 'employee', '01.01.2013', '30.04.2015'

      201503, 3, '01.01.2013','30.04.2015', 'employee', '01.01.2013', '30.04.2015'];

       

       

      In the third line #1 switches from apprentice to employee and the apprentice date should end with 14.2.2015 but the date is missing in the data.

       

      Any way how i can check the data during script execution und fill missing dates with next starting date -1 of the same EmployeeID ?