7 Replies Latest reply: Sep 26, 2014 10:54 AM by Dave Riley RSS

    Multiple lookup

    Paul Steinborn

      Hi Community,

       

      how can i do a multiple lookup?

       

      I tried following without luck :

       

      Lookup ('ComponentName', 'ProductID', Lookup ('ProductCategory', 'ProductID', ProductID, 'ProductTable') , 'ComponentTable')

        • Re: Multiple lookup
          anbu cheliyan

          Can you show script and sample data.

            • Re: Multiple lookup
              Paul Steinborn

              Hi,

               

              cant show sample data, script is just loading three tables and making an lookup at one of this tables (Product).

              With the result of this lookup i want to lookup at the second table (Component).

               

              Additionaly when doing a simple lookup i will get erverytime only one result.

              But there are also use cases where more than one value can be looked up, how to achive this?

                • Re: Multiple lookup
                  Deepak Tibhe

                  try applymap function, experts please correct me if I am wrong.

                   

                   

                  Thanks

                  • Re: Multiple lookup
                    anbu cheliyan

                    Your example doesn't look correct. Your inner lookup retrieves ProductCategory based on ProductID and the same is used against ProductID in outer lookup?

                      • Re: Multiple lookup
                        Paul Steinborn

                        Yes it should be like this,


                        Lookup ('ComponentName', 'ComponentID', Lookup ('ComponentID', 'ProductID', ProductID, 'ProductTable'), 'ComponentTable')


                        but that still not change the fact that it seems that nested lookups are not possible in qlikview


                          • Re: Multiple lookup
                            Dave Riley

                            I don't think that is the case, the example below seems to work.

                             

                            ProductTable:

                            Load * inline [
                            ProductID, ProductCategory, ComponentID
                            1, A, 123
                            2, B, 123
                            3, A, 333
                            4, C, 541
                            5, B, 221
                            ]
                            ;

                            ComponentTable:
                            Load * inline [
                            ComponentID,ComponentName
                            123, ABC
                            333, DEF
                            541, GHI
                            221, JKL
                            ]
                            ;

                            Data:
                            Load
                            Rowno() as ID,
                            Lookup('ComponentID','ProductID',rowno(),'ProductTable') as ID_output_lkp1,
                            Lookup('ComponentName','ComponentID',Lookup('ComponentID','ProductID',rowno(),'ProductTable'),'ComponentTable') as ID_output_lkp2
                            autogenerate 5;

                             

                             

                            I am sure there is a better way of doing what you want unless you can give more info to suggest otherwise. ApplyMap or left joins should do what you need.

                             

                             

                            flipside