topic Re: Counting Employees Over Time in QlikView

Counting Employees Over Time

Anonymous — Thu, 07 Dec 2017 22:46:12 GMT

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:

Employee ID	Action Type	Position Start Date	Action Effective Date
901	Accession	1/1/2011	1/1/2011
901	Award	1/1/2011	6/4/2011
901	Rating Change	1/1/2011	8/9/2011
901	Departure	1/1/2011	5/1/2012
901	Accession	9/1/2014	9/1/2014
901	Promotion	9/1/2014	8/30/2015
901	Increase	9/1/2014	2/9/2016
901	Departure	9/1/2014	12/1/2016

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!

Re: Counting Employees Over Time

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:12:30 GMT

And your expected result from provided table?

Re: Counting Employees Over Time

Anonymous — Fri, 08 Dec 2017 03:08:49 GMT

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.

Re: Counting Employees Over Time

Anonymous — Fri, 08 Dec 2017 11:25:06 GMT

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;