2 Replies Latest reply: Apr 17, 2018 11:56 AM by August Dénes RSS

    lookup() function misses values

    August Dénes


      Im new to this platform and im trying to utilize the lookup function to create a new column/field. However, when I enter a field as match_field_value the function fails to return 2 values I know exist. If I try to look specifically for these two missing values the function is able to return the correct values. See example







      [Namn] AS [Sheet1$-3.Namn],




      [VismaAktörId] AS [Anstnr-VismaAktörId],

      APPLYMAP( '__cityKey2GeoPoint', APPLYMAP( '__cityName2Key', LOWER([OrtNamn])), '-') AS [Sheet1$-3.OrtNamn_GeoInfo]

      FROM [lib://Data/Organisation.xls]

      (biff, embedded labels, table is Sheet1$);



      LOAD *,

      Lookup('Sheet1$-3.Namn','Anstnr-VismaAktörId',[ChefNr]) as ChefNamn //test

      Resident [Sheet1$_a3b33b80-6a51-0727-b43f-226dc5b0];

      DROP Table [Sheet1$_a3b33b80-6a51-0727-b43f-226dc5b0];


      Here Lookup fails to return values for ChefNr = 102 , ChefNr = 141. But if I instead type

      Lookup('Sheet1$-3.Namn','Anstnr-VismaAktörId','141') as ChefNamn

      The correct value is returned, any suggestions as to what I'm doing wrong would be greatly appreciated!

        • Re: lookup() function misses values
          chaitanya yelisetty

          Lookup Syntax Seems to be bit off for me in the above script.


          Can you try to use like the below :


          lookup(field_name, match_field_name, match_field_value [, table_name])

          put the table name as well please and also

          for ChefNr = 141 how many different values exist in table [Sheet1$_a3b33b80-6a51-0727-b43f-226dc5b0] ?

          if there are more than 1 value is matching in the source table for 141 and 102 we may not get the correct results .