Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I'm hoping for some ideas on how I can go about counting on-board employees over time for an initiative I'm working on. I've played with a couple of loops and peeks and haven't yet gotten the result I'm looking for. My data are structured as follows:
| |||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
What I need to do is create an object that counts this employee (901) as present in years 2011, 2012, 2014, 2015 and 2016 but not 2013 as they departed in May 2012 and was rehired in September 2014 so they were not on board in 2013.
The data table contains all employees ever to work in the organization, including current employees who have not separated and many like the above example where there are separate segments of service. I know I can use an iterator to count the employees by year if I can get the data structured such that I have a record for each segment of service but I'm struggling to transform my data into that format.
Any thoughts?
Much appreciated!
And your expected result from provided table?
With the approach I'm taking now I'd like to be able to return a table that displays one row per service segment. Using the above example I'd like to get:
Employee ID | Segment Start | Segment End |
901 | 1/1/2011 | 5/1/2012 |
901 | 9/1/2014 | 12/1/2016 |
If I have each segment like that I can run an iteration to count over years. The trick is getting it that way.
The approach I'm thinking to use is to load all segment end dates for each employee by loading every row where the action type is equal to departure. That isn't difficult. The part I'm having trouble implementing in the load script is the segment start date. The logic is that I need to find the earliest position start date prior to a separation without hitting another separation.
Try this once...
T1:
Load * Inline
[
Id,Type,Date1,Date2
901,A,12/8/2015,1/6/2016
901,B,1/7/2016,7/24/2016
901,C,7/25/2016,8/3/2016
901,D,8/4/2016,8/9/2016
901,A,8/10/2017,9/8/2017
901,B,9/9/2017,3/27/2018
901,C,3/28/2018,4/6/2018
901,D,4/7/2018,4/7/2019
];
NoConcatenate
T2:
Load * ,rowno() as rownum,recno() as recnum
Resident T1
where Type = 'A' or Type = 'D';
drop table T1;
NoConcatenate
T3:
Load *, if(recnum=1,Date1) as MinDate1,
if(recnum=4,Date2) as MaxDate1,
if(recnum=5,Date1) as MinDate2,
if(recnum=8,Date2) as MaxDate2
Resident T2;
drop table T2;
NoConcatenate
T5:
Load * , if(not isnull(MinDate1),MinDate1, if( not isnull(MaxDate1), MaxDate1,
if(not isnull(MinDate2),MinDate2, if( not isnull(MaxDate2), MaxDate2)))) as Date
Resident T3;
drop table T3;
NoConcatenate
T6:
Load Id,Date as StartDate,
If(rownum =1,PeeK(Date,1,'T5')) as Endate1,
If(rownum =3,PeeK(Date,3,'T5')) as Endate2
Resident T5;
drop table T5;
NoConcatenate
T7:
Load Id,StartDate,Endate1 as EndDate
Resident T6
where not Isnull(Endate1);
//drop Field Endate2;
//drop table T6;
Join(T7)
T8:
Load Id,StartDate,Endate2 as EndDate
Resident T6
where not Isnull(Endate2);
drop table T6;