Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;