topic Get Department by max end date but the same year in App Development

Get Department by max end date but the same year

Saif1 — Sun, 20 Nov 2022 23:15:15 GMT

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

Re: Get Department by max end date but the same year

BrunPierre — Mon, 21 Nov 2022 00:44:34 GMT

Hi, Try this

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

Resident Emp
Group By Comp_key;

Re: Get Department by max end date but the same year

Saif1 — Mon, 21 Nov 2022 11:47:05 GMT

saldy it returned wrong department per year

any other solutions? or maybe group by other things?

Re: Get Department by max end date but the same year

BrunPierre — Mon, 21 Nov 2022 12:16:01 GMT

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;

Re: Get Department by max end date but the same year

Saif1 — Tue, 22 Nov 2022 05:01:03 GMT

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.

Re: Get Department by max end date but the same year

BrunPierre — Tue, 22 Nov 2022 10:45:59 GMT

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

Elaborate more by sharing what exactly is expected.

Re: Get Department by max end date but the same year

Saif1 — Tue, 22 Nov 2022 10:55:55 GMT

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

Re: Get Department by max end date but the same year

Saif1 — Tue, 22 Nov 2022 10:56:33 GMT

anyways thank you for your help