match

sagarrahul · ‎2018-05-22

hiii need a small help

i am having certain names

Anil Kumar

anil

kumar

anil k

k anil

kumar anil

kumar a

wanna search them in my CUST_FULL_NAME and POL_INSURER_NAME column with all combination of name given up if match then output should out as below

CUST_FULL_NAME	output	CUST_MOBILE_NUMBER	CUST_EMAIL	POL_INSURER_NAME	name
anil(matching)	Anil	123	AVULAGIRI@GMAIL.COM	A GIRI
cghj		123	AVULAGIRI@GMAIL.COM	kumar a(yes present in list)	kumar a
anil kumar(yes present in list)	anil kumar	547	AVULAGIRI@GMAIL.COM	ANUSHA ADAVI
kumar anil (yes present in list)	kumar anil	445	AVULAGIRI@GMAIL.COM	GIRI ADAVI
A GIRI		258	AVULAGIRI@GMAIL.COM	LASYA PRIYA ADAVI
adfgh		55	AVULAGIRI@GMAIL.COM	anil kumar(yes present in list)	anil kumar

sagarrahul · ‎2018-05-22

want its expression

sagarrahul · ‎2018-05-22

want it expression

micheledenardi · ‎2018-05-22

if(march(upper(CUST_FULL_NAME),'ANIL KUMAR','ANIL','KUMAR','ANIL K','K ANIL','KUMAR ANIL','KUMAR A'),CUST_FULL_NAME,null()) as output

and

if(march(upper(POL_INSURER_NAME),'ANIL KUMAR','ANIL','KUMAR','ANIL K','K ANIL','KUMAR ANIL','KUMAR A'),CUST_FULL_NAME,null()) as name

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

jonathandienst · ‎2018-05-22

This is a data cleanup question as far as I can see,

One way that works well is to create a mapping table of "dirty" names to "clean", like this:

MAP_CLEANNAMES:

Mapping LOAD * Inline

[

Name1, Name2

Anil Kumar, Anil Kumar

anil, Anil Kumar

kumar, Anil Kumar

anil k, Anil Kumar

k anil, Anil Kumar

kumar anil, Anil Kumar

kumar a, Anil Kumar

...

];

Add other dirty - clean mappings as required. This is an inline load, but you can load from a spreadsheet or a database table using the same logic.

Then use the mapping when you load the name:

LOAD ...

Name,

ApplyMap('MAP_CLEANNAMES', Name, Name) as CleanName,

...

This is more extensible and easier to maintain than hardcoding the cleaning in the expression

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

sagarrahul · ‎2018-05-22

thank you for ur help but i can do loading process

i want in expression

sagarrahul · ‎2018-05-22

can we use len function n find below