Self Join kind-of

[Saravanan_Desingh]

Hello-

I have a situation here.

Let my Data is like this.

Emp	Mgr	City
e1	m1	c1
e2	m1	c1
e3	m1	c2
m1	m2	c3
e4	m2	c2
m2	m3	c1
m3	m1	c3

I need the output like this:

Mgr	City	Count
m1	c3	4
m2	c1	2
m3	c3	1

Basically, Manager's City and Head Count under him. I know, AGGR like functions can help in doing Self-Join. But not good in that subject. Can someone help me? thank you.

[sunny_talwar]

May be this?

[Saravanan_Desingh]

No, Sunny.

For m2 & m3, City is wrong. We have to see Mgr in Emp Column and then take the corresponding City.

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabEmp:
Hierarchy(Emp,Mgr,EmpName,,Emp,'EmpPath',,)
LOAD Emp,
    Emp as EmpName,
    Mgr,
    City
FROM [https://community.qlik.com/thread/198753] (html, codepage is 1252, embedded labels, table is @1);

Left Join (tabEmp)
LOAD Emp as Mgr,
    City as MgrCity
Resident tabEmp;

DROP Fields EmpName;

The hierarchy load isn't necessary to get your expected result but might add additional selection (treeview) and visualization possibilities to your application.

The difference in the employee count for m2 compared to your expected result is caused by a loop in your sample data, i.e. m2 can't have his manager's manager as an employee (m1->m3->m2->m1).

hope this helps

regards

Marco

[Saravanan_Desingh]

Nice learning for me. Thanks Marco.

[MarcoWedel]

you're welcome

please close your thread if your question is answered.

thanks

regards

Marco