Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have two tables for employee record keeping
TABLE1
Employee | Action | StartDate | EndDate |
---|---|---|---|
A | Redesignation | 10-10-2011 | 31-12-9999 |
B | Hiring | 12-10-2011 | 31-12-9999 |
TABLE2
Employee | StartDate | EndDate | Department |
---|---|---|---|
A | 10-10-2011 | 31-12-9999 | ABC |
A | 01-01-1990 | 09-10-2011 | XYZ |
The employee "A' is hired in 'XYZ' department and worked there from his hiring to 09-10-2011 and redesignated to dept 'ABC' from 10-10-2011 .
How to find a table containng the complete transfer info for an employee.
Employee TransferOn FRomDept ToDept
qualify *;
Unqulify Employee;
table1:
Load
Employee,
Action,
Start Date,
enddate
from Table1
table2:
load
Employee,
Start Date,
enddate
,
Depaartment
from Table 2
hope this help
thanks sunil
my fieldnames are not same but the data is same, how this qualify will work in my case??
Qualify joins the similar data or column having same name??
TABLE1
Employee.TAB1 | Action.TAB1 | StartDate.TAB1 | EndDate.TAB1 |
---|---|---|---|
A | Redesignation | 10-10-2011 | 31-12-9999 |
B | Hiring | 12-10-2011 | 31-12-9999 |
TABLE2
Employee.TAB2 | StartDate.TAB2 | EndDate.TAB2 | Department.TAB2 |
---|---|---|---|
A | 10-10-2011 | 31-12-9999 | ABC |
A | 01-01-1990 | 09-10-2011 | XYZ |
table1:
Load
Employee.Tab1 AS Employee,
Action.Tab1,
Start Date.Tab1,
enddate.Tab1
from Table1
table2:
load
Employee.Tab2 AS Employee,
Start Date,.Tab2,
enddate.Tab2
,
Depaartment
from Table 2
Try This Method
use tab1 like below
in Table1 rename
no need to use qualify
Employee.TAB1 as Employee.TAB2
hope this help
i am afraid this is not serving the purpose.i want a new table in this format:
Employee TransferOn FRomDept ToDept
Hi,
TempTable:
Load
Employee,
Action,
[Start Date],
enddate
from Table1;
Left Join
load
Employee,
[Start Date] AS Tab2StartDate,
enddate AS Tab2EndDate,
Department
from Table 2;
TempData:
LOAD
Employee,
Tab2StartDate AS TransferOn,
If(Employee = previous(Employee) AND Action = 'ReDesignation', Previous(Department)) AS FromDepartment,
If(Employee = previous(Employee) AND Action = 'ReDesignation', Department, 'Temp') AS ToDepartment
RESIDENT TempTable
ORDER BY Tab2StartDate;
FinalData:
LOAD
*
RESIDENT TempData
WHERE ToDepartment <> 'Temp';
Drop table TempData;
DROP TABLE TempTable;
Hope this helps you.
Regards,
Jagan.
thank u jagan for ur reply.
but m really afraid this is not serving the purpose..
can u plz look into it??
Thanks & Regards