use value from another table if matched

[mulan1]

I have 2 tables:

Employees:
LOAD
 EmpID,
 Department,
 StartDate
FROM [lib://......QVD](qvd);

EmpDeparture:
LOAD
 EmpID,  
 DepartureDate,
FROM [lib:.....QVD](qvd);

EmpDeparture - if the emp has left the company, he will have a row with the date of departure.
If emp still working, he will not have a row in this table.

Employees - If the emp moved department , he will have another row with the new department and the start date in that department.

I need to create an end date based on the start date of the previous startDate.
For employee who does not have an end date - i want to look for a departure date in EmpDeparture table. If no match is found (emp still working in the firm), I want to put today's date.

for exmple: 
EmpID |  Department | StartDate | EndDate
1                       A            1/1/2024      1/5/2024(from EmpDeparture)

2                       A             3/8/2024     27/9/2024(today)

2                       B             2/4/2024    2/8/2024

2                       C             2/1/2023   1/4/2024

Let's assume that emp 1 left the company and emp 2 is still working for the company

I tried this logic -  I get the dates correctly from the previous function but all the others endDate get today(), even if they no longer work in the company and have departure date:

EmpDepartureMap// to EmpOrderBy:

MAPPING LOAD 

     DepartureDate,

     EmpID  

FROM [lib://....QVD](qvd);

NoConcatenate //to Employees

EmpOrderBy:

Load *,

if(EmpID=Previous(EmpID),

   Previous(StartDate)-1, 

   if( not isnull( ApplyMap('EmpDepartureMap', EmpID) ),

       ApplyMap('EmpDepartureMap', EmpID, today() ))

       )AS EndDate;

Load *

RESIDENT Employees

ORDER BY EmpID,StartDate DESC;

DROP TABLE Employees;

Can't figure out why it doesn't work.
Also I thought at first maybe to use IntervalMatch but I came to the conclusion that this is a wrong choice

Many thanks in advance to everyone

[Kushal_Chawda]

@mulan1  try below.

map_departure:
Mapping load 
             EmpID,
             DepartureDate
FROM [lib:.....QVD](qvd);

Employee:
Load EmpID, 
     Department,
     StartDate
FROM [lib://......QVD](qvd);

Employee_Final:
Load *,
     date(if(EmpID<>Previous(EmpID), applymap('map_departure',EmpID,Today()), Previous(StartDate)-1)) as EndDate
Resident Employee
Order by EmpID,StartDate desc;

Drop Tables Employee;