9 Replies Latest reply: Jun 24, 2016 3:37 AM by Ridhaa Hendricks RSS

    Lookup() Function

    Ridhaa Hendricks

      Hi there

       

      Is there a way to use the lookup function to lookup an exact field within a wildcard field.

      in the example below I would like to return the sales value for the CarName='Bmw' in the Cars table the NameofCar='2009 bmW 325i'

      This is the logic:

      Lookup('fieldname','matchfieldname',matchfieldvalue,'tablename')

       

      Lookup('Salesvalue','CarName',*NameofCar*,'Sales')


      I would like to make my search dynamic


      Thank you

        • Re: Lookup() Function
          Marcus Sommer

          I don't think that this will be possible and I doubt that these approach makes much sense then lookup will always return the first matching. Maybe it's better to map/join/associate those tables over a field like "CarName".

           

          - Marcus

            • Re: Lookup() Function
              Ridhaa Hendricks

              Hi Marcus

               

              Thank you for your reply

               

              This is basically what I am trying to do. with the attached data

              I would like to return the below table basically get the sales value for each NameOfCar from Sales table

              The condition is, As long as NameOfCar from the Cars table contains the CarName from the Sales table, then return the Sales value for car name in line with NameOfCar in the Cars table.

              How would we do this in Qlikview? in excel we could do a vlookup ...

               

                  

              NameOfCarClassPerformanceSalesValue
              2009 bmW 325iAExcellent5000
              Toyota Corolla 1.3CBad1000
              Volvo S4AGoodN/A
              Mini CooperBAverageN/A
              Kia PicantoCBad2000

               

              Your assistance is appreciated.

                • Re: Lookup() Function
                  Sunny Talwar

                  Try this code:

                   

                  MappingSales:

                  Mapping

                  LOAD Upper(CarName),

                      '/' & SalesValue & '\'

                  FROM

                  Cars.xlsx

                  (ooxml, embedded labels, table is Sales);

                   

                  Cars:

                  LOAD NameOfCar,

                      Class,

                      Performance,

                      If(Len(Trim(TextBetween(MapSubString('MappingSales', Upper(NameOfCar)), '/', '\'))) = 0, 'N/A',

                        TextBetween(MapSubString('MappingSales', Upper(NameOfCar)), '/', '\')) as SalesValue

                  FROM

                  Cars.xlsx

                  (ooxml, embedded labels, table is Cars);


                  Capture.PNG