Using lookup

[#1]

Hello everyone, I am new on this forum and have one (possibly very simple) question.

I have a source file (list of workers) with a couple of columns including worker's name and his/her supervisor's name (written in exactly the same format). How can I create an additional column showing the name of manager's manager?

I tried lookup('Manager','Name',Manager) but for some reason it did not work.

Do you know the solution?

Thanks,

Marcin

[MarcoWedel]

test data

employee	manager
name1	name3
name2	name3
name3	name4
name4	CEO
name5	name7
name6	name7
name7	name4
name8	name7
name9	name7
name10	name1
name11	name1
name12	name2
name13	name2
name14	name6
name15	name6
name16	name14
name17	name14
name18	name15
name19	name15

[MarcoWedel · Accepted solution]

Hi Dariusz,

I tried to combine both the Hierarchy and the HierarchyBelongsTo prefix in order to see if that leads to a more flexible solution.

This is the result:

tabOrganization:
Hierarchy(employeeID, managerID, employee, ParentName, employee, organization, '/', Level)
LOAD
  employee,
  AutoNumber(employee) as employeeID,
  manager,
  AutoNumber(manager) as managerID;
LOAD employee, 
     manager
FROM [http://community.qlik.com/thread/112800]
(html, codepage is 1252, embedded labels, table is @1);

tabManagerLevel:
HierarchyBelongsTo(employeeID, managerID, employeeName, mgrID, mgrName, mgrLevel)
LOAD
  employee as employeeName,
  employeeID,
  managerID
Resident tabOrganization;

It looks like this enhances the selection possibilities, i.e. you now can select all employees with a common manager, all employees on a specific level, all employees upwards and downwards from a specific level.

The list of all managers managers can also be easily created by selecting the "mgrLevel" 2.

thanks for your interesting suggestion, I learned a lot.

regards

Marco

[Anonymous]

I've never really implement Hierarchy list boxes to a 100% satisfactory level in an application but

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies

and

Hierarchies

are two great resources.

[#5]

Marco,

You are right. Combination of Hierarchy and HierarchyBelongsTo gives a lot

possibilities

I like HierarchyBelongsTo very much. It is very useful in cases, where you

need to transform business process data, ABC costing or supply chain, as

source data often includes parent-child table to represent process stages.

Regards

Darek

03-04-2014 02:05 użytkownik "Marco Wedel" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Re: Using lookup

reply from Marco Wedel<http://community.qlik.com/people/MarcoWedel?et=watches.email.thread>in

Scripting - View the full discussion<http://community.qlik.com/message/501147?et=watches.email.thread#501147>

[#6]

Hello again! I must say I am thrilled as I didn't expected so many answers! Thank you

I was busy for the past two days and I only managed to try the first solution (right join) which worked for me (although I had to filter the results by the distance from CEO, luckily this column was in my set of data). As of now I don't understand the more complex solutions (as mentioned I am a beginner in the QlikView world), but I promise to play with them during the weekend.

Thank you once again!

I might have one additional question: is it possible to count the total number of subordinates (including subordinates' subordinates)? I was thinking about creating a drill-down pie-chart.

Cheers!

Marcin

[MarcoWedel]

Hi Marcin,

in order to get the number of subordinates and the different manager levels of an employee you could extend this solution like this:

tabOrganization:
Hierarchy(employeeID, managerID, employee, ParentName, employee, organization, '/', Level)
LOAD
  employee,
  AutoNumber(employee) as employeeID,
  manager,
  AutoNumber(manager) as managerID;
LOAD employee,
    manager
FROM [http://community.qlik.com/thread/112800]
(html, codepage is 1252, embedded labels, table is @1);

tabManagerLevel:
HierarchyBelongsTo(employeeID, managerID, employeeName, mgrID, mgrName, mgrLevel)
LOAD
  employee as employeeName,
  employeeID,
  managerID
Resident tabOrganization;

tabTemp:
Generic
LOAD
  employeeID,
  'manager'&mgrLevel,
  mgrName
Resident tabManagerLevel;

FOR i = 0 to NoOfTables()
  TableList:
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1
  WHERE WildMatch(TableName($(i)), 'tabTemp.manager*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
  LET vTable = FieldValue('Tablename', $(i));
  LEFT JOIN (tabOrganization) LOAD * RESIDENT $(vTable);
  DROP TABLE $(vTable);
NEXT i

DROP TABLE TableList;

Left Join (tabOrganization)
LOAD
  mgrID as employeeID,
  Count(DISTINCT employeeID) as NumOfSubordinates
Resident tabManagerLevel
Where mgrLevel > 0
Group By mgrID;

This script uses a generic load to create the managerX fields and a technique described by Rob Wunderlich to combine the generated tables into one: (Qlikview Notes: Use cases for Generic Load)

regards

Marco