Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Probably the following link will help. http://community.qlik.com/message/131099#131099 Regards Siva Sankar
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.
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
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