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:




      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 ...



              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:




                  LOAD Upper(CarName),

                      '/' & SalesValue & '\'



                  (ooxml, embedded labels, table is Sales);



                  LOAD NameOfCar,



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

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



                  (ooxml, embedded labels, table is Cars);