topic Re: Levenshtein in QlikView

Levenshtein

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

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'

Re: Levenshtein

its_anandrjs — Fri, 08 Sep 2017 16:42:58 GMT

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'

Re: Levenshtein

vishsaggi — Fri, 08 Sep 2017 17:03:11 GMT

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'

Re: Levenshtein

danielnevitt — Fri, 08 Sep 2017 17:07:32 GMT

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

Re: Levenshtein

its_anandrjs — Fri, 08 Sep 2017 17:20:16 GMT

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

Re: Levenshtein

swuehl — Fri, 08 Sep 2017 17:50:05 GMT

You can use a mapping table approach:

Data Cleansing