Discussion Board for collaboration on QlikView Scripting.
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?
Solved! Go to Solution.
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:
Hierarchy(employeeID, managerID, employee, ParentName, employee, organization, '/', Level)
AutoNumber(employee) as employeeID,
AutoNumber(manager) as managerID;
(html, codepage is 1252, embedded labels, table is @1);
HierarchyBelongsTo(employeeID, managerID, employeeName, mgrID, mgrName, mgrLevel)
employee as employeeName,
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.
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;
load Employee as Manager, Manager ManagersManager as from Emplist;
Not sure if right join is correct. I think it would be left join in your example but I'm not 100% sure.
Applymap('ManagersManagerMapping',Manager) as ManagersManager
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
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:
Hierarchy (employee, manager, employeeName, ParentName, employee, organization, '/', Level)
employee as employeeName;
LOAD * Inline [
hope this helps
if you prefer having the managers_manager as an additional field, then add
if(Level>=3,SubField(organization, '/', Level-2), '-') as managers_manager
to your load script
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.
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.