topic Re: Self Join kind-of in QlikView

Self Join kind-of

Saravanan_Desingh — Thu, 07 Jan 2016 16:33:07 GMT

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.

Re: Self Join kind-of

sunny_talwar — Thu, 07 Jan 2016 17:08:51 GMT

May be this?

Re: Self Join kind-of

Saravanan_Desingh — Thu, 07 Jan 2016 17:12:38 GMT

No, Sunny.

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

Re: Self Join kind-of

MarcoWedel — Fri, 08 Jan 2016 00:48:00 GMT

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

Re: Self Join kind-of

Saravanan_Desingh — Fri, 08 Jan 2016 03:08:28 GMT

Nice learning for me. Thanks Marco.

Re: Self Join kind-of

MarcoWedel — Fri, 08 Jan 2016 23:36:02 GMT

you're welcome

please close your thread if your question is answered.

thanks

regards

Marco