Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Counting Employees Over Time

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 IDAction TypePosition Start DateAction Effective Date
901Accession1/1/20111/1/2011
901Award1/1/20116/4/2011
901Rating Change1/1/20118/9/2011
901Departure1/1/20115/1/2012
901Accession9/1/20149/1/2014
901Promotion9/1/20148/30/2015
901Increase9/1/20142/9/2016
901Departure9/1/201412/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!

3 Replies
Anil_Babu_Samineni

And your expected result from provided table?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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 IDSegment StartSegment End
9011/1/20115/1/2012
9019/1/201412/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.

Anonymous
Not applicable
Author

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;