Employee Log

[Sharbel]

Hello,

I built an app that performs the followings: 

Each day it loads an excel file that contains a list of active employees  and then saves the file into QVD file and names it emp_20250109 (the key 20250109 is derived from the file date 09/01/2025)

On the next day another excel file is loaded  and then  saved in into another QVD file named  emp_20250110 and so on...

If certain employee was included in emp_20250109 but not inemp_20250110 , that means the employee went Non_active on 10/01/2025.

The purpose of the app is eventually to count in each month how many employees went Non_active.

Any ideas on how to approach this ?

Best regards,

Sharbel 

[Bhushan_Mahajan]

@Sharbel If you are looking whether the employee is active for the day or not the you can create flags date wise. And if you want to see whether employee left the organization then you can handle it using slowly changing dimension or creating the active period for that employee.

[Sharbel]

Suppose we have the initial employee log table as  follows: 

the desired output table would look like this :

Tried interval match , but unfortunately didn't work for me 

Any ideas ?

Sharbel

[rpennacchi]

You can do that creating a cartesian product between all possible dates and employees and after that you exclude the active employees on each date. I will attach a screenshot of my script and then real the script below:

Active_Employees:
Load
EmployeeID
, FileDate
, EmployeeID & '-' & FileDate as Key_Employe_Date // This will be used to filter active employees
Inline [
EmployeeID , FileDate
101 , 09/01/2025
102 , 09/01/2025
101 , 10/01/2025
];

// Creating a table with all possible dates betwen start and end
// **(you can use a different start and end date)

TMP_First_and_Last_Dates:
Load
Min(FileDate) as First_Date
, Max(FileDate) as Last_Date
Resident Active_Employees;

let vFirst_Date = Peek('First_Date', 0, 'TMP_First_and_Last_Dates');
let vLast_Date = Peek('Last_Date' , 0, 'TMP_First_and_Last_Dates');

Drop Table TMP_First_and_Last_Dates;

All_Employees_All_Dates:
Load
Date('$(vFirst_Date)' + RowNo() - 1) as All_Date
Autogenerate '$(vLast_Date)' - '$(vFirst_Date)' + 1;

// Creating a Cartesian Prodcut to gel all possible dates with all possible employees

Left Join(All_Employees_All_Dates)
Load Distinct EmployeeID as All_EmployeeID Resident Active_Employees;

// Excluding records where we have and active employee

Fact_Employees_Inactivity:
Load
All_EmployeeID as EmployeeID
, All_Date as Inactive_Date
Resident All_Employees_All_Dates
Where Not Exists(Key_Employe_Date, All_EmployeeID & '-' & All_Date);

Drop Table Active_Employees, All_Employees_All_Dates;

[Sharbel]

the above script works fine whenever there are only 2 employees ,

yet it doesn't work on tables with multiple employees since it returns more than one combination  of EmployeeID and InActive_Date instead of returning only one combination per each employee.