Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

adenes94
New Contributor

lookup() function misses values

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!

Tags (1)
2 Replies
ychaitanya
Contributor III

Re: lookup() function misses values

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 .



adenes94
New Contributor

Re: lookup() function misses values

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.

Community Browser