Concatenating 2 sources

robin_heijt — Mon, 05 Nov 2018 12:08:58 GMT

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!

topic Concatenating 2 sources in App Development

Concatenating 2 sources