Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
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;
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
Hi Rod
thank you for your helpr, finally figured it out using peek, but despite that same solution as yours.