Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Sunny,
Try: If(exists(Cityname, place)=-1,'Yes','No') As FieldName
Best Regards,
Nilo
Hi Sunny,
Try: If(exists(Cityname, place)=-1,'Yes','No') As FieldName
Best Regards,
Nilo
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
Nilo has it!
hi Nilo,
Many thanks for your help.
Is it possible to get the count as well?
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