topic Re: String Percentage Comparison? in QlikView

String Percentage Comparison?

cbaqir — Tue, 13 Jun 2017 21:15:53 GMT

Is there a way to get QV to compare how similar two values are?

In other words, match on 75% of the string to return possible duplicates?

John Doe - ABCD

John Doe - EFGH

Re: String Percentage Comparison?

Peter_Cammaert — Tue, 13 Jun 2017 21:31:01 GMT

As a starter to detect string similarity, see the implementation of the Levenshtein Distance algorithm here: string matching with fuzzy, trigram (n-gram), levenshtein, etc.

Background info can be found here: Levenshtein distance - Wikipedia

Re: String Percentage Comparison?

MarcoWedel — Tue, 13 Jun 2017 21:52:57 GMT

can you post some more examples?

Is it always the first part of the string you're interested in?

regards

Marco

Re: String Percentage Comparison?

cbaqir — Wed, 14 Jun 2017 15:36:28 GMT

It's not always the first part of the string. Here's an example where there is an extra space in the middle:

MBS - Modified Barium Swallow Study

Here's another example not at the end:

Transphenoidal (Pituitary) Surgery Postoperative

Transsphenoidal (Pituitary) Surgery Postoperative

Example < vs less than:

POC Risk for Nutritional Imbalance: < Requirements (Newborn)

POC Risk for Nutritional Imbalance: < Requirements (NICU)

POC Risk for Nutritional Imbalance: less than Requirements (Newborn)

POC Risk for Nutritional Imbalance: less than Requirements (NICU)

Re: String Percentage Comparison?

cbaqir — Wed, 14 Jun 2017 16:45:54 GMT

I've attached sample data. I ultimately want to show a list that shows matches between PLAN_DESC at like a 75% match.

Re: String Percentage Comparison?

MarcoWedel — Thu, 15 Jun 2017 00:43:25 GMT

Hi,

maybe one solution might be:

tabPlanTemp:
LOAD Distinct
    PLAN_DESC
FROM [https://community.qlik.com/servlet/JiveServlet/download/1287827-283097/POWER%20PLAN%20COUNTS.xlsx] (ooxml, embedded labels, table is [PLAN COUNT EXAMPLE])
Where Len(Trim(PLAN_DESC));

Join
LOAD PLAN_DESC as PLAN_DESC2
Resident tabPlanTemp;

tabPlan:
LOAD PLAN_DESC,
    PLAN_DESC2,
    Num(1-Levenshtein(PLAN_DESC,PLAN_DESC2)/RangeMax(Len(PLAN_DESC),Len(PLAN_DESC2)),'0.0%') as Similarity
Resident tabPlanTemp
Where not PLAN_DESC follows PLAN_DESC2;

DROP Table tabPlanTemp;

Another example:

Fuzzy Matching/Joining

hope this helps

regards

Marco

Re: String Percentage Comparison?

cbaqir — Thu, 15 Jun 2017 14:48:03 GMT

Super cool. Thanks!

scottford‌ clondono‌

Re: String Percentage Comparison?

MarcoWedel — Thu, 15 Jun 2017 19:02:23 GMT

you're welcome

regards

Marco

Re: String Percentage Comparison?

cbaqir — Wed, 21 Jun 2017 20:12:48 GMT

The load and drop of the temp table takes a very long time. Is there a way I can use a QVD to speed up the process? I've never tried a QVD on top of a temp table.

Re: String Percentage Comparison?

cbaqir — Thu, 29 Jun 2017 19:55:20 GMT

The load and drop of the temp table takes a very long time. Is there a way I can use a QVD to speed up the process? I've never tried a QVD on top of a temp table.

Re: String Percentage Comparison?

cbaqir — Mon, 31 Jul 2017 15:33:29 GMT

marcowedel‌

I tried loading my temp table from a QVD but it still takes more than an hour to refresh. Any suggestions on how to speed it up?

tabPlanTemp:
LOAD Distinct
DESCRIPTION
FROM [..\External_Data\PP_USAGE_ECISA.xlsx] (ooxml, embedded labels, table is [CCG_PLANS])
Where Len(Trim(DESCRIPTION));

Join
LOAD DESCRIPTION as PLAN_DESC2
Resident tabPlanTemp;

tabPlan:
LOAD DESCRIPTION,
PLAN_DESC2,
Num(1-Levenshtein(DESCRIPTION,PLAN_DESC2)/RangeMax(Len(DESCRIPTION),Len(PLAN_DESC2)),'0.0%') as Similarity
Resident tabPlanTemp
Where not DESCRIPTION follows PLAN_DESC2;

//tabPlanTemp:
//LOAD DESCRIPTION,
// PLAN_DESC2
//FROM
//[..\QVD\01_Extract_QVD\EKG PowerPlan.QVD]
//(qvd);
//

DROP Table tabPlanTemp;