Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
nilo_pontes
Partner - Creator
Partner - Creator

Hi Sunny,


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


Best Regards,


Nilo

View solution in original post

5 Replies
nilo_pontes
Partner - Creator
Partner - Creator

Hi Sunny,


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


Best Regards,


Nilo

its_anandrjs

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

martynlloyd
Partner - Creator III
Partner - Creator III

Nilo has it!

Not applicable
Author

hi Nilo,


Many thanks for your help.

Is it possible to get the count as well?

nilo_pontes
Partner - Creator
Partner - Creator

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