Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarrahul
Creator
Creator

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
Creator
Creator
Author

want its expression

sagarrahul
Creator
Creator
Author

want it expression

micheledenardi
Specialist II
Specialist II

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
Partner - Champion III
Partner - Champion III

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
Creator
Creator
Author

thank you for ur help but i can do loading process

i want in expression

sagarrahul
Creator
Creator
Author

can we use len function n find below

Anil Kumar

anil

kumar

anil k

k anil

kumar anil

kumar a