Lookup Function

[#1]

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?

[nilo_pontes · Accepted solution]

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

Hope this helps

[martynlloyd]

Nilo has it!

[#5]

hi Nilo,

Many thanks for your help.

Is it possible to get the count as well?

[nilo_pontes]

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