table join

[#1]

i have two tables for employee record keeping

• Action table, keeping all the actions performed on employee eg hiring, redesignation, leaving
• Organizational assignment table, keeping the data of the organizational assignments of the employee

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

[SunilChauhan]

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

[#3]

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

[perumal_41]

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

[SunilChauhan]

use tab1 like below

in Table1 rename

no need to use qualify

Employee.TAB1 as  Employee.TAB2

hope this help

[#6]

i am afraid this is not serving the purpose.i want a new table in this format:

Employee     TransferOn     FRomDept     ToDept

[jagan]

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.

[#8]

thank u jagan for ur reply.
but m really afraid this is not serving the purpose..
can u plz look into it??

Thanks & Regards