Error correction - missing date in script

[#1]

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 ?

[#2]

I think i got it as long as i can guarantee the correct sort order .

If anyone looking for a similar task:

LOAD *,

    IF(EmployeeID = peek('EmployeeID') and isnull(EmpRelEndDate) AND EmpRelStartDate <> peek('EmpRelStartDate'),

    date(peek('EmpRelStartDate')-1),

    EmpRelEndDate) as NewEnd

Resident TEST

Order by  EmployeeID, EmpRelStartDate desc, Period desc;

[#3 · Accepted solution]

Hi Markus,

Here is a really simple example of what I think you are trying to do, for this example I have created a new field called NewEmpRelEndDate.

Hope this helps.

Kind regards,

Rod

[#4]

Hi Rod

thank you for your helpr, finally figured it out using peek, but despite that same solution as yours.