topic Re: How to create a function or Script for string mapping with percentages in QlikView

How to create a function or Script for string mapping with percentages

bobbyn4u — Mon, 29 May 2017 13:56:15 GMT

I would like to compare the names in the same column, for this I want to write a code in the script editor or a function in the object. can anyone help me on this?

Below is the example:

Account Names:

Accenture Pvt Ltd

Accenture Private Limited

Hitachi Systems Ltd

Hitachi System Limited

Us Department of Army

United States Department of Army

Education Services‌New to Qlik Sense‌Blogs‌

Re: How to create a function or Script for string mapping with percentages

sunny_talwar — Wed, 28 Jun 2017 17:29:01 GMT

What is the expected output here?

Re: How to create a function or Script for string mapping with percentages

bobbyn4u — Thu, 29 Jun 2017 08:42:31 GMT

Hi Sunny,

We have one dataset, from that dataset (Like self-join in SQL) itself we need to identify possible matches with some %ge criteria.

Dataset:

Account Names:

Accenture Pvt Ltd

Accenture Private Limited

Hitachi Systems Ltd

Hitachi System Limited

Us Department of Army

United States Department of Army

Expected output should be like...

Account Names	AccountName_Match
Accenture Pvt Ltd	Accenture Pvt Ltd
Accenture Pvt Ltd	Accenture Private Limited
Accenture Private Limited	Accenture Private Limited
Accenture Private Limited	Accenture Pvt Ltd
Hitachi Systems Ltd	Hitachi Systems Ltd
Hitachi Systems Ltd	Hitachi System Limited
Hitachi System Limited	Hitachi System Limited
Hitachi System Limited	Hitachi Systems Ltd
Us Department of Army	Us Department of Army
Us Department of Army	United States Department of Army
United States Department of Army	United States Department of Army
United States Department of Army	Us Department of Army

and the %ge match.

Re: How to create a function or Script for string mapping with percentages

sunny_talwar — Thu, 29 Jun 2017 08:58:00 GMT

I think this is something marcowedel‌ might be able to help you with.

Re: How to create a function or Script for string mapping with percentages

Kushal_Chawda — Thu, 29 Jun 2017 09:43:26 GMT

may be this will help

http://qlikviewcookbook.com/?attachment_id=72

Re: How to create a function or Script for string mapping with percentages

cheenu_janakira — Thu, 29 Jun 2017 18:22:05 GMT

Check attached QVW. Your solution can be used with a combo of Wildmatch and ApplyMap (or alteratively with MapSubString for exact string matches - however, this method is 'case sensitive', the Wildmatch solution below isn't 'case sensitive').

The solution in this QVW allows you to look for a string of sequenced letters if it is a 'free text' field you are searching through and you believe that there is a minimum amount of letter sequence needed to match the ultimate result/grouping you want to get.

(Can't attach QVW, so script is below, copy/paste exactly and play around with the "WildmatchStrings_Temp" table and change the FACT table contents to see how it works. Put a table in front end with the 2 resulting fields to understand how it works).

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

//Script start here

WildmatchStrings_Temp:

//2. Create a numerical equivalent for each line/string of data in table:

LOAD *, Rowno() as OrdinalPosition;

//1. Original strings against which you are trying to match:

LOAD * INLINE [

Clean_Name_as_Category, WildMatchString

Accenture, accenture

Hitachi, hitachi

US Department of Army, u*s*d*p*t*army

];

//Create a field with all strings which you will put in a variable and is sequentially 'ordered' as it is in above table:

Var_WildMatchString:

LOAD CHR(39) & '*' & Concat(DISTINCT WildMatchString, '*' & CHR(39) & ',' & CHR(39) & '*', OrdinalPosition) & '*' & CHR(39) as Var_WildMatchString RESIDENT WildmatchStrings_Temp;

//Insert value in var:

LET vVar_WildMatchString = PEEK('Var_WildMatchString', 0, 'Var_WildMatchString');

DROP TABLE Var_WildMatchString;

//Reload table as Mapping table:

WildMatch_Map:

MAPPING LOAD

OrdinalPosition,

Clean_Name_as_Category

RESIDENT WildmatchStrings_Temp;

DROP TABLE WildmatchStrings_Temp;

THE_FACT_TABLE:

LOAD

ApplyMap('WildMatch_Map', WildMatchOrdinalPosition) as Clean_Name_as_Category

;

LOAD

Wildmatch(AccountNames, $(vVar_WildMatchString)) as WildMatchOrdinalPosition,

MapSubString(

;

LOAD * INLINE [

AccountNames

Accenture Pvt Ltd

Accenture Private Limited

Accenture Consulting

Hitachi Systems Ltd

Hitachi System Limited

Hitachi France

Us Department of Army

United States Department of Army

us dept army

];

//end script here

Re: How to create a function or Script for string mapping with percentages

rwunderlich — Thu, 29 Jun 2017 18:34:03 GMT

You could approach this as a Wildcard mapping problem. An example of Mapping with Wildcards can be found here.

Qlikview Cookbook: Mapping With Wildcards http://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

You can also use the Qvc.CreateWildmapExpression routine in QVC http://qlikviewcomponents.org which does the same thing as the example.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to create a function or Script for string mapping with percentages

effinty2112 — Thu, 29 Jun 2017 21:03:24 GMT

Hi,

I'd be happy to be proven wrong but I think this goes beyond the normal scope of a BI application. If your set of account names runs to a large number with all sorts of variations in how the names are recorded then you may need an application (think R, Matlab, Octave or something along those lines) running an algorithm that has been suitably trained.

Good luck

Andrew

Re: How to create a function or Script for string mapping with percentages

rwunderlich — Thu, 29 Jun 2017 21:55:06 GMT

Andrew,

I run across this requirement fairly often in QV. It's usually just as the OP's example, variations on company names. I tackle it with a mapping table or wildcard mapping.

-Rob

Re: How to create a function or Script for string mapping with percentages

MarcoWedel — Thu, 29 Jun 2017 23:35:23 GMT

Hi,

maybe this approach matches your requirements?

String Percentage Comparison?

Using your example one solution might look like:

tabAccountNamesTemp:
LOAD * INLINE [
    AccountName
    Accenture Pvt Ltd
    Accenture Private Limited
    Hitachi Systems Ltd
    Hitachi System Limited
    Us Department of Army
    United States Department of Army 
];

Join
LOAD AccountName as AccountName_Match
Resident tabAccountNamesTemp;

tabAccountNames:
LOAD AccountName,
    AccountName_Match,
    Num(1-Levenshtein(AccountName,AccountName_Match)/RangeMax(Len(AccountName),Len(AccountName_Match)),'0.0%') as Similarity
Resident tabAccountNamesTemp;

DROP Table tabAccountNamesTemp;

hope this helps

regards

Marco

Re: How to create a function or Script for string mapping with percentages

effinty2112 — Fri, 30 Jun 2017 09:14:05 GMT

Marco,

This is terrific. I've had a longstanding problem involving two companies in our group each having a customer database with an overlap of customers but with the details being entered often with slightly different names. I've been putting off teasing out all the common customers but maybe I can tackle that now.

Many thanks

Andrew