Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Lookup Function

Hi All,

I want to apply lookup and check if field is already added in above table. Please see the details below. 

LOAD * INLINE [
Cityname
Delhi,
Pune,
Bihar,
]
;

Directory;
LOAD place,
' here i want to add new field yes or no. if place exists in citynames(in above table) return yes else no 
FROM
[..\..\sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Can anyone please help me to add the expression in load syntax?

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Lookup Function

Hi Sunny,


Try: If(exists(Cityname, place)=-1,'Yes','No') As FieldName


Best Regards,


Nilo

View solution in original post

5 Replies
Partner
Partner

Re: Lookup Function

Hi Sunny,


Try: If(exists(Cityname, place)=-1,'Yes','No') As FieldName


Best Regards,


Nilo

View solution in original post

Re: Lookup Function

Try to load your table like see below small example

T1:

LOAD * INLINE [

Cityname

Delhi,

Pune,

Bihar ];

T2:

LOAD * Inline [

place

Delhi

Pune

India ];

New:

LOAD

Cityname as City_name

Resident T1;

Inner Join

LOAD

place as City_name,

'Y' as Flag

Resident T2;

Concatenate

LOAD

place as City_name,

'N' as Flag

Resident T2 Where Not Exists(City_name,place);

Then you get output as

OP.png

Hope this helps

Partner
Partner

Re: Lookup Function

Nilo has it!

Not applicable

Re: Lookup Function

hi Nilo,


Many thanks for your help.

Is it possible to get the count as well?

Partner
Partner

Re: Lookup Function

Hi Sunny, you're welcome.

Do you want count how many 'yes'es and 'no's you've got after load?

In this case you can create a pie chart or a straight table and count(FieldName).

Regards,

Nilo