topic Re: Using lookup in QlikView

Using lookup

Tue, 01 Apr 2014 20:07:13 GMT

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

Re: Using lookup

Tue, 01 Apr 2014 20:18:27 GMT

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;

Re: Using lookup

Anonymous — Tue, 01 Apr 2014 21:26:14 GMT

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;

Re: Using lookup

Tue, 01 Apr 2014 21:42:21 GMT

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

MarcoWedel — Tue, 01 Apr 2014 22:40:15 GMT

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

Re: Re: Re: Using lookup

MarcoWedel — Tue, 01 Apr 2014 22:49:17 GMT

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

Re: Re: Re: Using lookup

Wed, 02 Apr 2014 08:00:38 GMT

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

MarcoWedel — Wed, 02 Apr 2014 08:41:08 GMT

you are right, this should be even better.

Maybe we can add this to the solution?

regards

Marco

Re: Using lookup

Wed, 02 Apr 2014 08:50:17 GMT

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

Re: Using lookup

Wed, 02 Apr 2014 14:12:57 GMT

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

Re: Re: Using lookup

MarcoWedel — Wed, 02 Apr 2014 23:21:05 GMT

test data

employee	manager
name1	name3
name2	name3
name3	name4
name4	CEO
name5	name7
name6	name7
name7	name4
name8	name7
name9	name7
name10	name1
name11	name1
name12	name2
name13	name2
name14	name6
name15	name6
name16	name14
name17	name14
name18	name15
name19	name15

Re: Re: Using lookup

MarcoWedel — Thu, 03 Apr 2014 00:04:45 GMT

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

Re: Using lookup

Anonymous — Thu, 03 Apr 2014 01:38:23 GMT

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.

Re: Using lookup

Thu, 03 Apr 2014 07:50:59 GMT

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>

Re: Using lookup

Thu, 03 Apr 2014 20:33:00 GMT

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

Re: Re: Using lookup

MarcoWedel — Sun, 06 Apr 2014 22:24:51 GMT

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;

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