Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I need some help please. I am trying to compare the head count between year-end 2018 (December 2018) with Nov 2019 (Max year/month that I have uploaded). I created a sample table for reference.
Month | Employee_ID | Employe_Name |
Dec-18 | 1100 | Adam |
Dec-18 | 1101 | Alex |
Dec-18 | 1112 | David |
Dec-18 | 1115 | John |
Jan-19 | 1100 | Adam |
Jan-19 | 1101 | Alex |
Jan-19 | 1112 | David |
etc. | etc. | etc. |
etc. | etc. | etc. |
etc. | etc. | etc. |
etc. | etc. | etc. |
Nov-19 | 1100 | Adam |
Nov-19 | 1112 | David |
Nov-19 | 1200 | Ken |
Nov-19 | 1201 | Kevin |
Nov-19 | 1202 | Sam |
I would like to somehow compare or match Employee_ID for the two months. If Employee_ID appears in Dec-2018 and not in Nov-2019 that means its a Termination. And if the Employee_ID appears in Nov-19 and not in Dec-18 it is an Addition. End goal is to get the count of Termination and Addition based on the above logic. So comparing the two months (dec 2018 and Nov 2019) we should get 3 additions (Employee_ID: 1200, 1201, 1202) and 2 terminations (Employee_ID: 1101,1115).
I will eventually put this in a waterfall.
Can someone please help me with it. Thanks 🙂
This would be easiest in the load script. Something like:
[Emp Temp]:
LOAD
Employee_ID, Month,Employee_Name,If(Previous(Employee_ID)=Employee_ID,If(isnull(Previous(Month),1) AS Addition;
LOAD
Employee_ID,Month,Employee_Name
FROM Table1
ORDER BY Employee_ID,Month;
[Emp]:
NoConcatenate
LOAD Employee_ID,Month,Employee_Name, Addition, If(Isnull(Previous(Month),1) AS Termination
RESIDENT [Emp Tem]
ORDER BY Employee_D, Month desc;