topic table join in QlikView

table join

Fri, 24 Feb 2012 10:30:35 GMT

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

table join

SunilChauhan — Fri, 24 Feb 2012 10:35:21 GMT

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

table join

Fri, 24 Feb 2012 10:44:26 GMT

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

table join

perumal_41 — Fri, 24 Feb 2012 11:19:30 GMT

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

table join

SunilChauhan — Fri, 24 Feb 2012 11:20:44 GMT

use tab1 like below

in Table1 rename

no need to use qualify

Employee.TAB1 as Employee.TAB2

hope this help

table join

Fri, 24 Feb 2012 11:44:09 GMT

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

Employee TransferOn FRomDept ToDept

table join

jagan — Fri, 24 Feb 2012 12:24:50 GMT

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.

table join

Fri, 18 May 2012 06:26:13 GMT

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

Thanks & Regards