Solved: Re: Using lookup - Qlik Community

Report Inappropriate Content · ‎2014-04-01

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 · ‎2014-04-02

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

View solution in original post

Report Inappropriate Content · ‎2014-04-01

you mean you have one file with fields like Employee, Manager and wanted to add another field ManagersManager

you can something like this

load Employee, Manager from Emplist;

right join

load Employee as Manager, Manager ManagersManager as from Emplist;

Anonymous · ‎2014-04-01

Not sure if right join is correct. I think it would be left join in your example but I'm not 100% sure.

Alternatively:

ManagersManagerMapping:

MAPPING LOAD

Name,

Manager

From Emplist;

LOAD

Name,
Manager,

Applymap('ManagersManagerMapping',Manager) as ManagersManager

From Emplist;

Report Inappropriate Content · ‎2014-04-01

Maybe you would like to use "Hierarchy" function?

It can transform parent-child hierarchy into table, where in each row you will have as many columns as your hierachy has levels. So, it may find for you even manager/manager/manager.... name

MarcoWedel · ‎2014-04-01

Hi Marcin, hi Dariusz,

that's also been the first thing that came to my mind.

This solution could look like this:

given this table:

the Hierarchy prefix could create this result:

the manager_manager column in this case is an expression to extract the manager's manager out of the organization field.

=if(Level>=3,SubField(organization, '/', Level-2), '-')

The additional code is minimal:

tabOrganization:

Hierarchy (employee, manager, employeeName, ParentName, employee, organization, '/', Level)

LOAD

employee,

manager,

employee as employeeName;

LOAD * Inline [

employee, manager

name1,name3

name3,name4

name7,name4

name4,CEO

name9,name7

name2,name3

name5,name7

name8,name7

name6,name7

name10,name1

name11,name1

name12,name2

name13,name2

name14,name6

name15,name6

name16,name14

name17,name14

name18,name15

name19,name15

];

hope this helps

regards

Marco

MarcoWedel · ‎2014-04-01

if you prefer having the managers_manager as an additional field, then add

Left Join
LOAD
  employeeName,
  if(Level>=3,SubField(organization, '/', Level-2), '-') as managers_manager
Resident tabOrganization;

to your load script

regards

Marco

Report Inappropriate Content · ‎2014-04-02

Let me add one possibility useful in transforming the hierarchical structures. Perhaps you do not use it in this case, but keep in mind about its existence. I mean HierachyBelngsTo function.

The prefix Creates a table containing all ancestor-child relations of the hierarchy. The ancestor fields can then be used to select entire trees in the hierarchy. The output table in most cases contains several records per node. An additional field containing the depth difference of the nodes can be created.

In conclusion, if, as you described in the question, looking for a way to find the name of one manager, then use methods such as join or applymap.
On the other hand if you want to transform a source in a dynamic way, which shows how the whole structure of the hierarchy, you can use the prefix Hierarchy or HierarchyBelongsTo, depending on which model will be most convenient for further analysis.

Have fun with QV!

regards

Darek

MarcoWedel · ‎2014-04-02

you are right, this should be even better.

Maybe we can add this to the solution?

regards

Marco

Report Inappropriate Content · ‎2014-04-02

You want me to prepare or find example? Of course i can

But maybe it is good time, that kwarcekkwarcek tells us if she/he likes our ideas or asks some detailed questions if she/he meets some problems trying to immplement solution in application.

regards

Darek

Report Inappropriate Content · ‎2014-04-02

Yes my bad... i meant to type left join.