lookup() function misses values

[Anonymous]

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!

[ychaitanya]

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 .

[Anonymous]

Hi, thank you for the reply, using the table name

Lookup('Sheet1$-3.Namn','Anstnr-VismaAktörId',[ChefNr],'[Sheet1$_a3b33b80-6a51-0727-b43f-226dc5b0]') as ChefNamn //test still does not provide the missing values. There is also only 1 entry for both of the missing values, i.e only on occurence of 102 and 141.