15 Replies Latest reply: Apr 6, 2014 6:28 PM by Marco Wedel RSS

    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

        • 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;

          • Re: Using lookup
            Dariusz Mielczarek

            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
                Marco Wedel

                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
                    Marco Wedel

                    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
                        Dariusz Mielczarek

                        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

                    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
                        Marco Wedel

                        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;
                        
                        

                         

                        QlikCommunity_Thread_112800_Pic10.JPG.jpg

                         

                        QlikCommunity_Thread_112800_Pic9.JPG.jpg

                         

                        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