Custom Colum with multiple conditions , Hire and Re hire

[Irek]

Hi, 

Need to create conditional column and it is a little bit to complex for me 🙂

As in below table. 

I have columns Employee Id, process type, process status and event date, result will be mine new column. 

Goal is to show which employee was hire and re hire if have two or more hires against his ID. 

Hire is when process type is Hire or Contract Employee and Status is Successfully Completed and it first appear in report. 

If same employee have another case with same condition then should be consider as Re hire. 

I'm trying to write it but so far without result. 

Thanks in advance for help

Marcin

employee_id	business_process_type	status	event_date	RESULT
1	Change Job	Successfully Completed	01.04.2023
1	Hire	Successfully Completed	01.02.2021	New Hire
1	Hire	Successfully Completed	01.05.2021	Re Hire
2	Hire	Canceled	01.05.2021
2	Change Job	Successfully Completed	16.02.2023
2	Contract Employee	Successfully Completed	12.03.2023	New Hire
2	Change Job	Canceled	12.03.2023
8	Hire	Canceled	12.03.2023
9	Change Job	Successfully Completed	05.09.2023

[PijushRoy]

Hi @Irek 

Create a custom column and use similar code as per your requirement
= if [business_process_type] = "Hire" and [status] = "Successfully Completed" then "Hire" else 
if [business_process_type] = "2nd Condition" and [status] = "2nd Condition" then "Hire" else "Re Hire"

Please change the code as per your requirement

If solved, please mark as SOLUTION and give 👍

[Irek]

Hi Pijush, 

Thank you for your respond, 

Re hire should be only when process type is hire or contract, status is completed but employee have two or more lines with such conditions date is not the oldest. I mean that first hire is hire another one is re hire,