Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

1 Solution

Accepted Solutions

Re: Re: Using lookup

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

15 Replies
Not applicable

Re: Using lookup

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;

michael_gardner
Contributor III

Re: Using lookup

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;

Not applicable

Re: Using lookup

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

Re: Re: Using lookup

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:

QlikCommunity_Thread_112800_Pic3.JPG.jpg

the Hierarchy prefix could create this result:

QlikCommunity_Thread_112800_Pic1.JPG.jpg

QlikCommunity_Thread_112800_Pic2.JPG.jpg

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

Re: Re: Re: Using lookup

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

Not applicable

Re: Re: Re: Using lookup

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

Re: Using lookup

you are right, this should be even better.

Maybe we can add this to the solution?

regards

Marco

Not applicable

Re: Using lookup

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

But maybe it is good time, that   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

Not applicable

Re: Using lookup

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

Community Browser