check the value exists in another table

[pala_jagadeesh]

Hi,

one table has values like below,

How can we check  asha@gmail.com is present or not

if it is present the value shows as yes

Skype

abc@gmail.com;abc.d@gmail.com;d.abc@gmail.com

anu@gmail.com;anu.sree@gmail.com;sree.anu@gmail.com

asha@gmail.com;asha.d@gmail.com;d.asha@gmail.com

cone@gmail.com;cone.su@gmail.com;su.cone@gmail.com

jey@gmail.com

I want Output like below

Output

Mail	Flag
abc@gmail.com	Yes
anu.sree@gmail.com	Yes
d.asha@gmail.com	Yes
seema@gmail.com	No
jey@gmail.com	Yes

[abhijitnalekar]

Hi @pala_jagadeesh ,

Are the values in the Skype column in a single row or on multiple rows?

[pala_jagadeesh]

Hi @abhijitnalekar ,

There 5 rows in Skype column

[sidhiq91]

@pala_jagadeesh  I am not very much clear with your requirement, if you wanted to check only asha@gmail.com

then why is your flag says Yes even for other values. Could you please elaborate on your requirement for us to work?

[pala_jagadeesh]

@sidhiq91

If we compare Skype column , with below mail column,

if mail column mails are present in skype column, for those we want create flag as 1 or yes

Mail

abc@gmail.com

anu.sree@gmail.com

d.asha@gmail.com

seema@gmail.com

Jey@gmail.com

Here except seema@gmail.com, remaining all are present in skype coumn,

so how to create flag for them

[Iswarya_]

Hi @pala_jagadeesh :

Try as below:

Skype:
LOAD
Subfield(Mail,';') as Mail
Inline [
Mail
abc@gmail.com;abc.d@gmail.com;d.abc@gmail.com
anu@gmail.com;anu.sree@gmail.com;sree.anu@gmail.com
asha@gmail.com;asha.d@gmail.com;d.asha@gmail.com
cone@gmail.com;cone.su@gmail.com;su.cone@gmail.com
jey@gmail.com
];

Mail:
Load *,
'Yes' as Flag
Inline
[
Mail
abc@gmail.com
anu.sree@gmail.com
d.asha@gmail.com
seema@gmail.com
jey@gmail.com
]
where Exists(Mail);

Concatenate(Mail)
Mail:
Load *,
'No' as Flag
Inline
[
Mail
abc@gmail.com
anu.sree@gmail.com
d.asha@gmail.com
seema@gmail.com
jey@gmail.com
]
where not Exists(Mail);

Drop Table Skype;

Exit Script;

[Iswarya_ · Accepted solution]

You can also as per below script @pala_jagadeesh 

Skype_Temp:
LOAD
Subfield(Mail,';') as Mail,
Flag
Inline [
Mail, Flag
abc@gmail.com;abc.d@gmail.com;d.abc@gmail.com, Yes
anu@gmail.com;anu.sree@gmail.com;sree.anu@gmail.com, Yes
asha@gmail.com;asha.d@gmail.com;d.asha@gmail.com, Yes
cone@gmail.com;cone.su@gmail.com;su.cone@gmail.com, Yes
jey@gmail.com, Yes
];

Skype:
Mapping Load
Mail,
Flag
Resident Skype_Temp;
Drop Table Skype_Temp;

Mail:
lOAD *,
Applymap('Skype',Mail,'No') as Flag
Inline
[
Mail
abc@gmail.com
anu.sree@gmail.com
d.asha@gmail.com
seema@gmail.com
jey@gmail.com
];

exit Script

[pala_jagadeesh]

@Iswarya_ 

Thank you Iswarya, it's worked