topic Re: Self Join Help in QlikView

Self Join Help

Wed, 29 Jul 2015 04:54:37 GMT

Hi Team,

I have the following fields in a table :

EmpID, Name, MgrID, Salary

1, Tom, 4, 80000

2, Rose, B, 1, 8500

3, Tito, A, 4, 10000

4, Sandra, B, 1, 2000

I need to perform a self join on the above table

I have tried doing using Hierarchy function as below, but it is not working as expected.

HIERARCHY (EmployeeID, MgrID, Salary, Employee)

LOAD EmployeeId as EmployeeID,

MgrID,

Name as Employee,

Salary;

LOAD * INLINE [

EmployeeId, Name, MgrID, Salary

1, Tom, 4, 8000

2, Rose, 1, 8500

3, Tito, 4, 10000

4, Sandra, 1, 20000

];

OR using

LEFT JOIN Employee

LOAD

Emp.Id as Mgr.ID,

Name as "Manager Name"

RESIDENT Employee

Kindly guide me perform the same.

Thanks in advance.

Re: Self Join Help

sunny_talwar — Wed, 29 Jul 2015 04:59:29 GMT

This seems to be working:

Table:

LOAD * Inline [

EmpID, Name, MgrID, Salary

1, Tom, 4, 80000

2, Rose, 1, 8500

3, Tito, 4, 10000

4, Sandra, 1, 2000

];

Left Join(Table)

LOAD EmpID as MgrID,

Name as ManagerName

Resident Table;

Output:

Re: Self Join Help

tresB — Wed, 29 Jul 2015 05:02:22 GMT

Not really sure what you are trying to get. May be like you said:

Employee:

LOAD * INLINE [

EmployeeId, Name, MgrID, Salary

1, Tom, 4, 8000

2, Rose, 1, 8500

3, Tito, 4, 10000

4, Sandra, 1, 20000

];

LEFT JOIN Employee

LOAD

EmployeeId as MgrID,

Name as "Manager Name"

RESIDENT Employee;

Did you try that?

Re: Self Join Help

avinashelite — Wed, 29 Jul 2015 05:04:26 GMT

Hi Robin,

what's your exact requirement ?

try like this:

Hierarchy (EmployeeId, MgrID, Name,,Name,Hierarchy,'\',TreeDepth)

LOAD * INLINE [

EmployeeId, Name, MgrID, Salary

1, Tom, 4, 8000

2, Rose, 1, 8500

3, Tito, 4, 10000

4, Sandra, 1, 20000

];

try to understand the Hierarchy Parameters and its order

OR try like this

Employee:

LOAD * INLINE [

EmployeeId, Name, MgrID, Salary

1, Tom, 4, 8000

2, Rose, 1, 8500

3, Tito, 4, 10000

4, Sandra, 1, 20000

];

LOAD MgrID as EmployeeId,

Name as Manager_Name

resident

Employee;

Re: Self Join Help

jonathandienst — Wed, 29 Jul 2015 05:04:36 GMT

Perhaps because your data has a circular relationship - Tom's manager is Sandra. Sandra's manager is Tom... Sunny showed you can load this, but is it correct?