Adding Rows to Show Changes between to Year Periods

[carlcimino]

Hello I have a table of data (sample attached) that includes three elements - Employee ID, Department and Year_Period.  What I am looking to do is have a process that looks between the Year_Period and creates records that are categorized as noted in the Action column.  The conditions for each Action are noted in the table below.  I know I have to utilize peek and previous functions and possibly some temp tables but could use some assistance setting it up.  Any help is much appreciated it.

Order	Condition 1	Condition 2	Condition 3	Condition 4	Action
1	EmployeeID NOT in previous Year_Period	N/A	EmployeeID NOT in next Year_Period	N/A	Gain/Loss
2	EmployeeID IS in previous Year_Period	EmployeeID+Dept NOT in previous Year_Period	EmployeeID NOT in next Year_Period	N/A	Transfer In/Loss
3	EmployeeID IS in previous Year_Period	EmployeeID+Dept IS in previous Year_Period	EmployeeID NOT in next Year_Period	N/A	Loss
4	EmployeeID NOT in previous Year_Period	N/A	EmployeeID IS in next Year_Period	EmployeeID+Dept NOT in next Year_Period	Gain/Transfer Out
5	EmployeeID NOT in previous Year_Period	N/A	EmployeeID IS in next Year_Period	EmployeeID+Dept IS in next Year_Period	Gain
6	EmployeeID IS in previous Year_Period	EmployeeID+Dept NOT in previous Year_Period	EmployeeID IS in next Year_Period	EmployeeID+Dept NOT in next Year_Period	Transfer In/Transfer Out
7	EmployeeID IS in previous Year_Period	EmployeeID+Dept NOT in previous Year_Period	EmployeeID IS in next Year_Period	EmployeeID+Dept IS in next Year_Period	Transfer In
8	EmployeeID IS in previous Year_Period	EmployeeID+Dept IS in previous Year_Period	EmployeeID IS in next Year_Period	EmployeeID+Dept NOT in next Year_Period	Transfer Out
Else					N/A

Here is what I have done so far...

Main:
LOAD
"Employee ID", //Key 1 for Gain/Loss
Department,
"Employee ID"&'-'&Department as TransferKey, //Create Key2 for Transfer In/Out
Year_Period
FROM [lib://Global_Finance_BIFileshare/Non-Application Related/Sample Data.xlsx]
(ooxml, embedded labels, table is Data);

//load distinct Employee IDs by Year_Period from resident to utilize
//ordering to enable peek/previous Employee ID from one Year_Period to the next

Key1:
load Distinct
"Employee ID",
Year_Period
Resident Main
order by "Employee ID",
Year_Period;

//load distinct TransferKey by Year_Period from resident to utilize
//ordering to enable peek/previous TransferKey from one Year_Period to the next

Key2:
Load Distinct
TransferKey,
Year_Period
Resident Main
order by TransferKey,
Year_Period;