Get Department by max end date but the same year

[Saif1]

Hello i have the following data: 

Comp_key	person_ID	emp_num	name	StartDate	EndDate	EmploymentYear	Department
102019	10	1	A	05/09/2019	06/10/2020	2019	HR
102020	10	1	A	05/09/2019	06/10/2020	2020	HR
102020	10	1	A	07/10/2020	31/12/4102	2020	Training
102021	10	1	A	07/10/2020	31/12/4102	2021	Training
102022	10	1	A	07/10/2020	31/12/4102	2022	Training
202020	20	2	B	07/08/2020	05/09/2021	2020	HR
202020	20	2	B	07/08/2020	05/09/2021	2021	HR
202021	20	2	B	06/09/2021	07/09/2022	2021	Training
202021	20	2	B	06/09/2021	07/09/2022	2022	Training
202022	20	2	B	08/09/2022	31/12/2022	2022	Procurement

Comp_key  = PersonID&EmploymentYear

as we can see in employee B he was in 2 departments in 2022 so i want to make a table with (max enddate of that year) and gives me that department table i want :

102019	10	1	A	05/09/2019	06/10/2020	2019	HR
102020	10	1	A	07/10/2020	31/12/4102	2020	Training
102021	10	1	A	07/10/2020	31/12/4102	2021	Training
102022	10	1	A	07/10/2020	31/12/4102	2022	Training
202020	20	2	B	07/08/2020	05/09/2021	2020	HR
202021	20	2	B	06/09/2021	07/09/2022	2021	Training
202022	20	2	B	08/09/2022	31/12/2022	2022	Procurement

what i have tried

Table2 :

load

Compkey,

Department as dept,

max(enddate) as maxenddate

resident Emp group by compkey,dept;

this doesnt give me my desired result 

any help ?

thanks

[BrunPierre]

Hi, Try this

Table2:
LOAD Comp_key,
MaxString(Department) as dept,
Date(Max(EndDate)) as maxenddate

Resident Emp
Group By Comp_key;

[Saif1]

saldy it returned wrong department per year

any other solutions? or maybe group by other things?

[BrunPierre]

NoConcatenate
Table2:
LOAD Comp_key,
     person_ID,
     emp_num,
     name,
     Date(Max(EndDate)) as EndDate,
     Date(Max(StartDate)) as StartDate,
     Min(EmploymentYear) as EmploymentYear,
     MaxString(Department) as Department

Resident Emp  
Group By Comp_key,person_ID,emp_num,name;

DROP Table Temp;

[Saif1]

your script is working , but it gives not the max department in the same year , lets imagine that Training department was in 2022 and procurment in 2022, your script is giving Training on all 2022, my goal is to make procurment for all 2022.

[BrunPierre]

@Saif1 The results of the script matches what you shared. Has your requirement changed?

Elaborate more by sharing what exactly is expected.

[Saif1]

what worked for me is to autonumber via the year and employee number and the department 

then getting the row number 1 as the highest in end year

[Saif1]

anyways thank you for your help