Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sagarrahul
Contributor

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
1 Reply
Partner
Partner

Re: match

HI Sagar,

The code i'm posting is a variation of my post here: Dependency Matrix‌‌, but instead of using dates, it uses strings to match (through the index function) if the text is present or not.

As a brief explanation:

// Gets a table with the possible names you need to check with

sub vC.TextFormats(TextMask,TableName)

// Gets in the TableName output table, the comparison between the names in InputTable to the String you want to compare to.

sub vC.TextSearch(String,InputTable,TableName)

// Loaded names table to check too

Data:

Load * Inline

[

CUST_FULL_NAME,POL_INSURER_NAME

anil,Anil

cghj,kumar a

anil kumar,ANUSHA ADAVI

kumar anil,GIRI ADAVI

A GIRI258,LASYA PRIYA ADAVI

adfgh,anil kumar

];

// Creates the table with the compared names

call vC.TextFormats('Anil Kumar;anil;kumar;anil k;k anil;kumar anil;kumar a','NamesTable');

// Loops all values for the CUST_FULL_NAME to check each individual name if it has a hit on the NamesTable

for each CustName in FieldValueList('CUST_FULL_NAME')

trace $(CustName);

call vC.TextSearch('$(CustName)','NamesTable','Match_Output');

next;

// Joins the output names to the data table

left join (Data)

Load

StringTest as CUST_FULL_NAME,

1 as [Found in field CUST_FULL_NAME]

Resident Match_Output;

// drops output table

drop table Match_Output;

// Loops all values for the CUST_FULL_NAME to check each individual name if it has a hit on the NamesTable

for each InsurerName in FieldValueList('POL_INSURER_NAME')

trace $(InsurerName);

call vC.TextSearch('$(InsurerName)','NamesTable','Match_Output');

next;

// Joins the output names to the data table

left join (Data)

Load

StringTest as POL_INSURER_NAME,

1 as [Found in field POL_INSURER_NAME]

Resident Match_Output;

// drops auxiliary tables

drop tables Match_Output,NamesTable;

As an output, I get the following:

sample.png

Saying that, for CUST_FULL_NAME there were 2 hits, and same for POL_INSURER_NAME.

Hope it helps,


Felipe.