topic Re: Looking for partial matched data in QlikView

Looking for partial matched data

Thu, 21 Jun 2012 14:06:26 GMT

Hello,

I've got two tables both contain a product Id but in one table the product Id maybe within a description field and/or may contain extra spaces.

I would like to load the two tables and show where there is a partial match from table 1 of a specific column ie product id in all the columns in table 2.

Is this possible ?

There is no actual input of the product Ids as this would go into 1,000 rows.

Ive looked at mixmatch but this doesnt seem to suit.

does anyone have any examples of this?

Re: Looking for partial matched data

Siva_Sankar — Thu, 21 Jun 2012 14:11:36 GMT

Probably the following link will help. http://community.qlik.com/message/131099#131099 Regards Siva Sankar

Re: Looking for partial matched data

swuehl — Thu, 21 Jun 2012 14:16:41 GMT

Would be nice to see some sample lines of data together with your expected outcome to better understand your requirements and setting.

>There is no actual input of the product Ids as this would go into 1,000 rows.

Sorry, I haven't understood this.

If you can tell any rules to read the product Ids from the description field, I would go for creating a new product ID field in table 2 by parsing the description field.

Re: Looking for partial matched data

Mon, 25 Jun 2012 09:50:56 GMT

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

Re: Looking for partial matched data

swuehl — Wed, 27 Jun 2012 12:51:18 GMT

I noticed that you created another thread, just for the records, here also the solution I just posted:

"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