Levenshtein

[danielnevitt]

Hi,

I have the below script.

I am trying to use the levenshtein logic on the BENEFICIARY_NAME AND Name fields.

BENEFICIARY_NAME is upper case ad contains some special characters.

Name is a mixture of upper and lower case and also contains some special charaters.

I'm sure there is a cleaner way to show both fields in upper case with no spaces or special characters.

Once I have done this, is it possible to apply levenshtein?  I have read quite a few forum posts, however I am slightly confused how to apply this.  Many years ago I did a similar thing using ACL.

Thanks for any help.

Regards,

Daniel

ODBC CONNECT32 TO [IQ CLONE];
LOAD *,
keepchar(BENEFICIARY_NAME,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as A,
keepchar (B,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as C,
levenshtein(A,C) as LevDistAC;
SQL select bi.*,
cm.TGTKEY1,cm.SRCKEY1,cm.TGTALTKEY,cm.isinactive,
p.name,
Upper(name) as B

FROM treasury_tier1.v_TRS_BANK_INS_TEMPLATE bi
left join crd_tier1.v_CIA_MAP cm on (convert(varchar,bi.RECEIVE_PARTY_NO) = cm.TGTKEY1)
left join crd_tier1.V_CRD_PARTY p on (convert(varchar,p.partyid) = cm.srckey1)
where cm.TGTSYSTEMID = 121
and cm.ENTITYCODE = 'PARTYROLE'

[its_anandrjs · Accepted solution]

Try this please A and C are field alias name thats why they are not able to use directly.

ODBC CONNECT32 TO [IQ CLONE];
LOAD *,
keepchar(BENEFICIARY_NAME,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as A,
keepchar (B,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as C,
levenshtein(keepchar(BENEFICIARY_NAME,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),keepchar (B,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') ) as LevDistAC;
SQL select bi.*,
cm.TGTKEY1,cm.SRCKEY1,cm.TGTALTKEY,cm.isinactive,
p.name,
Upper(name) as B

FROM treasury_tier1.v_TRS_BANK_INS_TEMPLATE bi
left join crd_tier1.v_CIA_MAP cm on (convert(varchar,bi.RECEIVE_PARTY_NO) = cm.TGTKEY1)
left join crd_tier1.V_CRD_PARTY p on (convert(varchar,p.partyid) = cm.srckey1)
where cm.TGTSYSTEMID = 121
and cm.ENTITYCODE = 'PARTYROLE'

[vishsaggi]

OR Will this work?.

ODBC CONNECT32 TO [IQ CLONE];

LOAD *,

        levenshtein(A, C) as LevDistAC;
LOAD *,
keepchar(BENEFICIARY_NAME,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as A,
keepchar (B,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as C;
SQL select bi.*,
cm.TGTKEY1,cm.SRCKEY1,cm.TGTALTKEY,cm.isinactive,
p.name,
Upper(name) as B

FROM treasury_tier1.v_TRS_BANK_INS_TEMPLATE bi
left join crd_tier1.v_CIA_MAP cm on (convert(varchar,bi.RECEIVE_PARTY_NO) = cm.TGTKEY1)
left join crd_tier1.V_CRD_PARTY p on (convert(varchar,p.partyid) = cm.srckey1)
where cm.TGTSYSTEMID = 121
and cm.ENTITYCODE = 'PARTYROLE'

[danielnevitt]

Thanks Anand, that works perfectly.

Do you know if there is a way that I can replace certain words with others.  For example:

LDA - Limitada

LTD - Limited

Thanks,

Daniel

[its_anandrjs]

Sorry no idea can you explain bit more about this what are they.

[swuehl]

You can use a mapping table approach:

Data Cleansing