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

[#2]

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]

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;

[#4]

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]

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]

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

[#7]

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]

you are right, this should be even better.

Maybe we can add this to the solution?

regards

Marco

[#9]

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

[#10]

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