Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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 ?

1 Solution

Accepted Solutions
Not applicable

Re: Error correction - missing date in script

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

3 Replies
Not applicable

Re: Error correction - missing date in script

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;

Not applicable

Re: Error correction - missing date in script

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

Not applicable

Re: Error correction - missing date in script

Hi Rod

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

Community Browser