Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using lookup

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

15 Replies
MarcoWedel

test data

employeemanager
name1name3
name2name3
name3name4
name4CEO
name5name7
name6name7
name7name4
name8name7
name9name7
name10name1
name11name1
name12name2
name13name2
name14name6
name15name6
name16name14
name17name14
name18name15
name19name15
MarcoWedel

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;

QlikCommunity_Thread_112800_Pic4.JPG.jpg

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.

QlikCommunity_Thread_112800_Pic5.JPG.jpg

QlikCommunity_Thread_112800_Pic6.JPG.jpg

QlikCommunity_Thread_112800_Pic7.JPG.jpg

QlikCommunity_Thread_112800_Pic8.JPG.jpg

thanks for your interesting suggestion, I learned a lot.

regards

Marco

Anonymous
Not applicable
Author

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.

Not applicable
Author

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>

Not applicable
Author

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;

QlikCommunity_Thread_112800_Pic10.JPG.jpg

QlikCommunity_Thread_112800_Pic9.JPG.jpg

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