Fuzzy Logic

[#1]

Hello,

I wish to use if possible fuzzy logic with Qlikview?

I want to find similar fields in two tables.

eg.

TABLE 1

ID, DESCRIPTION, PRICE

ABC123, Widget ALM, 101

ABD122, Wakka AKK, 80

ABC122, Widget ALK, 90

TABLE 2

ID, DESCRIPTION, PRICE

ABC0123, ALM Top, 90

03ndd, ABD122Wakka, 200

Colin, ABC0122Wid ALK, 30

Two tables of data. I would like to use Fuzzy logic basically using the desciption field to find similarity between the two tables as ID field is inconsistant in one table.

Is it possible to return matches between the two to produce an output like :

ID, DESCRIPTION, PRICE, ID2, DESCRIPTION2, PRICE2,

ABC123, Widget ALM, 101, ABC0123, ALM Top, 90

ABD122, Wakka AKK, 80, 03ndd, ABD122Wakka, 200

ABC122, Widget ALK, 90, Colin, ABC0122Wid ALK, 30

[swuehl]

A real fuzzy logic might be hard to implement in QV (of course you can check out e.g. VBScript macros that you can make use of). If a wildmatch comparison of the single Description words is enough (i.e. for ID ABC123, try matching  '*Widget*' or '*ALM*' with the DESCRIPTION2 field values), you can do it like this:

SET Del = '@!+';

TABLE1:

LOAD *, subfield(DESCRIPTION,' ') as DESCSUB INLINE [

ID, DESCRIPTION, PRICE

ABC123, Widget ALM, 101

ABD122, Wakka AKK, 80

ABC122, Widget ALK, 90

];

MAP1:

MAPPING

LOAD DESCSUB as Map1, '$(Del)' & ID &'$(Del)' Resident TABLE1;

TABLE2:

Left Join LOAD *, Textbetween(MapSubString('MAP1', DESCRIPTION2),'$(Del)','$(Del)') as ID INLINE [

ID2, DESCRIPTION2, PRICE2

ABC0123, ALM Top, 90

03ndd, ABD122Wakka, 200

Colin, ABC0122Wid ALK, 30

];

drop field DESCSUB;

The defined Del variable should be a sequence of characters that are not expected to appear in your DESCRIPTION fields.

Hope this helps,

Stefan