Concatenating 2 sources

[robin_heijt]

Hi,

As my source data I have 2 files:

Employee report:

ID	Status	Name	Hiring Date	Gender
1002	Active	John	24/01/2017	Male
8564	Active	Robbert	01/08/2018	Male
2054	Active	Patrick	01/01/2017	Male
5432	Withdrawn	Lena	01/01/2017	Female
3245	Withdrawn	Johnny	01/05/2017	Male

Movement Report:

ID	Status	Name	Effective Date	Movement Type
1002	Active	John	20/02/2017	Promotion
1002	Active	John	20/05/2018	Promotion
5432	Active	Lena	20/01/2018	Promotion
5432	Withdrawn	Lena	20/09/2018	Termination
3245	Withdrawn	Johnny	20/03/2018	Termination

I would then like to concatenate these 2 tables and create a field that represents the cycle they were part of.

With the desired result being this:

ID	Status	Name	Effective Date	Movement Type	Cycle
1002	Active	John	-	-	2018-2019
1002	Active	John	20/02/2017	Promotion	2016-2017
1002	Active	John	20/05/2018	Promotion	2017-2018
2054	Active	Patrick	-	-	2016-2017
2054	Active	Patrick	-	-	2017-2018
2054	Active	Patrick	-	-	2018-2019
5432	Active	Lena	-	-	2016-2017
5432	Active	Lena	20/04/2018	Promotion	2017-2018
5432	Withdrawn	Lena	20/09/2018	Termination	2018-2019
3245	Active	Johhny	-	-	2016-2017
3245	Withdrawn	Johnny	20/03/2018	Termination	2017-2018
8564	Active	Robbert	-	-	2018-2019

The most important part here is that each person will receive 1 line per cycle.

The field cycle will have to be a calculated or a manual field, as this is not present in any of the source files.

Currently I am doing it with this code:

Employee_Report:
LOAD
    "Global ID",
    Pers.No. as "Personal ID",
"Employee Status",
    Capitalize("First name"&' '& "Last name") as "Name",
    "Text" as Gender,
    Date(Date# (Grp.Entry, 'DD.MM.YYYY'), 'DD/MM/YYYY') as "Original hire date"
FROM [***/Employee Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

Outer Join (Employee_Report)
Movement:
Load
"Personal ID",
     "Name Movement",
"Employee Status",
"Effective Date",
     "Movement Type"

     Resident Movements;
     Drop table Movements;

then as resident:

Load *,
YearName("Effective Date", 0, 7) as "Cycle"
;
Load
  "Global ID",
"Personal ID",
"Employee Status",
Name,
Gender,
"Original hire date",
"Effective Date",
"Movement Type"
Resident Employee_Report;
Drop table Employee_Report;

What would I have to change or do to get the desired result with every person having 1 line per cycle and their corresponding movement?

Cheers!