Fill EndDate with the StartDate of the next DataSet

[jgbraun]

Hello,

I have a table with the employment relationships of employees.
This means that the table contains the personnel number, the employment relationship, the start date, end date and a change date.
Now, if a new record is created, the end date of the previous record is not necessarily set for the personnel number. But for my evaluation I need this, how can I set the date with the start date - 1 of the next record for the concerned personnel number?

Example:

PNR, employment relationship,startdate,enddate,change

111,trainee,2017-09-01,,2017-08-25

....

111,employee,2020-07-01,,2020-06-25

I want to load the record from 2017-08-25 so that in the endate it says 2020-06-30.
How can I do that?

Best regards 

Joerg

[stevejoyce]

You can use peek for this.  With no row_no parameter then it's the previous row (last row loaded).  You cannot look at the next row, because it hasn't been loaded yet.  So you need to reverse your sorting and look at previous row.

So i am sorting start_date descending and subtracting 1 on the previous record's start date if end_date is not provided.

So something like:

data:
Load
id
,date#(start_date, 'YYYY-MM-DD') as start_date

,date#(end_date, 'YYYY-MM-DD') as source_end_date;

load * inline [

id, start_date, end_date
2, 2017-09-10, 2017-09-15
2, 2017-01-01,
3, 2017-10-15,
];

calc_end_date:

load *
,if(len(source_end_date)=0, date(peek(start_date)-1, 'YYYY-MM-DD'), source_end_date) as end_date
resident data

order by start_date desc;

drop table data;

[jgbraun]

Hello,

if  I select only one id it works, but if there a more then one id in my table the date of the last end_date of one id is not valid, because it´s the start_date of the first dataset of the next id.

Some idee to solve this?

Best regards

Joerg

[stevejoyce]

Sorry i'm not following.  What do you mean if you select only one id?  What I had sent was a load script so doesn't get impacted by selection.

Maybe you can mock up or expand sample data in an inline table like I sent and highlight where the issue is.

[MarcoWedel · Accepted solution]

Hi,

you also have to check for the previous PNR being the same as the current PNR before assigning the previous startdate-1 (in descending order) as enddate, e.g like this:

tabEmplRelatTemp:
LOAD * INLINE [
    PNR, employment relationship, startdate , enddate   , change
    111, trainee                , 2017-09-01,           , 2017-08-25
    112, trainee                , 2018-10-01,           , 2018-09-26
    113, trainee                , 2019-11-01, 2021-09-30, 2019-10-28
    111, employee               , 2020-07-01,           , 2020-06-25
    112, employee               , 2021-08-01,           , 2018-07-27
    113, employee               , 2021-10-01,           , 2021-09-18
];

NoConcatenate
tabEmplRelat:
LOAD PNR,
     [employment relationship],
     startdate,
     If(enddate or PNR<>Previous(PNR), enddate, Date(Previous(startdate)-1)) as enddate,
     change
Resident tabEmplRelatTemp
Order By PNR, startdate desc;

DROP Table tabEmplRelatTemp;

hope this helps

regards

Marco

[jgbraun]

Thank you, that works great for me!

Joerg