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

    lookup() function misses values

    August Dénes

      Hi,

      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

       

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

      LOAD

      [Anställningsgrupp],

      [ChefNr],

      [Leverans],

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

      [Organisation],

      [OrtId],

      [OrtNamn],

      [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$);

       

      test1:

      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 .