Help with joining two tables

[adrianfer]

Hi, I need help joining these two resident tables - EMPLOYEE and LOCATION. 

EMPLOYEE

EmployeeId	ReferenceDate
111	1/8/19
111	2/8/19
111	3/8/19
111	4/8/19
111	5/8/19
222	1/8/19
222	2/8/19
222	3/8/19
222	4/8/19
222	5/8/19

LOCATION

EmployeeId	StartDate	EndDate	Location
111	2/8/19	3/8/19	AAA
111	4/8/19	5/8/19	BBB
222	1/8/19	4/8/19	AAA

The resulting table should be like this - it will have the same rows as the EMPLOYEE table plus 2 additional columns:

In_AAA = value will be 1 if Location is AAA and if the EMPLOYEE.ReferenceDate is between the LOCATION.StartDate and LOCATION.EndDate

In_BBB = value will be 1 if Location is BBB and if the ReferenceDate is between the LOCATION.StartDate and LOCATION.EndDate

EmployeeId	ReferenceDate	In_AAA	In_BBB
111	1/8/19	0	0
111	2/8/19	1	0
111	3/8/19	1	0
111	4/8/19	0	1
111	5/8/19	0	1
222	1/8/19	1	0
222	2/8/19	1	0
222	3/8/19	1	0
222	4/8/19	1	0
222	5/8/19	0	0

Thank you in advance!

[lironbaram · Accepted solution]

hi

this script will give you what you look for 

[Table]:
LOAD * INLINE
[
EmployeeId,ReferenceDate
111,1/8/19
111,2/8/19
111,3/8/19
111,4/8/19
111,5/8/19
222,1/8/19
222,2/8/19
222,3/8/19
222,4/8/19
222,5/8/19
](delimiter is ',');

[Table2]:
LOAD
[EmployeeId] ,
date([StartDate]+iterno()-1) as ReferenceDate,
[Location]
while date([StartDate]+iterno()-1)<=[EndDate];
LOAD * INLINE
[
EmployeeId,StartDate,EndDate,Location
111,2/8/19,3/8/19,AAA
111,4/8/19,5/8/19,BBB
222,1/8/19,4/8/19,AAA
](delimiter is ',');

//////////////////add location to table ///////
left join ([Table])
load [EmployeeId],
ReferenceDate,
[Location]
Resident [Table2];

drop Table Table2;
/////////////calculating ne fields /////////////
TableFinal:
load *,
if([Location]='AAA',1,0) as In_AAA,
if([Location]='BBB',1,0) as In_BBB
resident Table;

drop Table Table;

[adrianfer]

Thank you! that worked perfectly!