Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sagarrahul
New Contributor III

match

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_NAMEoutputCUST_MOBILE_NUMBERCUST_EMAILPOL_INSURER_NAMEname
anil(matching)Anil123AVULAGIRI@GMAIL.COMA GIRI
cghj123AVULAGIRI@GMAIL.COMkumar a(yes present in list)kumar a
anil kumar(yes present in list)anil kumar547AVULAGIRI@GMAIL.COMANUSHA ADAVI
kumar anil (yes present in list)kumar anil445AVULAGIRI@GMAIL.COMGIRI ADAVI
A GIRI258AVULAGIRI@GMAIL.COMLASYA PRIYA ADAVI
adfgh55AVULAGIRI@GMAIL.COManil kumar(yes present in list)anil kumar
6 Replies
sagarrahul
New Contributor III

Re: match

want its expression

sagarrahul
New Contributor III

Re: match

want it expression

micheledenardi
Valued Contributor

Re: match

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

MVP
MVP

Re: match

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

sagarrahul
New Contributor III

Re: match

thank you for ur help but i can do loading process

i want in expression

sagarrahul
New Contributor III

Re: match

can we use len function n find below

Anil Kumar

anil

kumar

anil k

k anil

kumar anil

kumar a