topic Re: Group by a column based on fuzzy match in QlikView

Group by a column based on fuzzy match

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts

I have a table with several data columns.

And one of the field is a string type. (This field is feed from a data source where free form description allowed)

SOURCE_TABLE:

F1, F2, F3

ABC , 100, $20000

abc Inc., 220, $25000

Abc Plastics, 230, $23000

3M, 100, 12000

3M Care, 175, 6500

And the real data contains 100s of variations of F1

In this example, I want the resultant table with F1 and F3, grouped by F1

RESULTANT_TABLE:

F1, F2

ABC, $68000

3M, $18500

Can the resultant F1 be stored in a mapping table

and

add resultant F1 as a column to the source table through apply map based on 'fuzzy' match ?

If so, can you pl share sample script ?

Also pl suggest other options to achieve this ?

Thanks,

Umashankar

Re: Group by a column based on fuzzy match

Anonymous — Sat, 25 Feb 2017 04:26:09 GMT

You have to create a lookup table for values in the F1 and the value it should take in the resultant table. Can use Applymap and create a table and group by.

TABLE:

load * inline [

F1, F2, F3

ABC , 100, $20000

abc Inc., 220, $25000

Abc Plastics, 230, $23000

3M, 100, 12000

3M Care, 175, 6500];

//Creating a mapping table

lookup:

Mapping

load * inline

[

F1,F1_value

ABC,ABC

abc Inc.,ABC

Abc Plastics,ABC

3M,3M

3M Care,3M];

FinalTable:

LEFT join(TABLE)

load F1,ApplyMap('lookup',F1) AS RES_F1 Resident TABLE;

lEFT JOIN(TABLE)

LOAD RES_F1,SUM(F3)

Resident TABLE

GROUP BY RES_F1;

Hope it helps!!

Re: Group by a column based on fuzzy match

ahaahaaha — Sat, 25 Feb 2017 05:53:07 GMT

Hi Umashankar,

As an variant

//load the source table

TABLE:

LOAD * Inline [

F1, F2, F3

ABC , 100, $20000

abc Inc., 220, $25000

Abc Plastics, 230, $23000

3M, 100, 12000

3M Care, 175, 6500];

NoConcatenate

SOURCE_TABLE:

LOAD

F1,

Lower(SubField(F1, ' ', 1)) as F1Key, //create key

F2,

Money#(F3, '$#') as F3 //convert money format

Resident TABLE;

Left Join

LOAD

F1Key,

Money(Sum(F3), '$ #,##') as SumF3 //convert money format

Resident SOURCE_TABLE

Group By F1Key;

DROP Table TABLE;

//Then you can remove the unneeded fields from the resulting table

//DROP Fields ... From SOURCE_TABLE;

Result

Regards,

Andrey

Re: Group by a column based on fuzzy match

umashankarus — Sat, 25 Feb 2017 06:34:58 GMT

Thanks for the reply

Since F1 variations are more, we would need dynamic expression to handle

Re: Group by a column based on fuzzy match

umashankarus — Sat, 25 Feb 2017 06:41:50 GMT

Thanks for the reply

The suggestion helps

Additionally, if there are data variations / spelling mismatch (within Subfield)....is there a way to handle by means of fuzzy match within the expression ?

example data for F1

BOTICELLI

BOTTICELLI Inc

BOTICELI Brands

Re: Group by a column based on fuzzy match

ahaahaaha — Sat, 25 Feb 2017 07:48:59 GMT

For this it is necessary to apply a mapping table. Look at this, please here Mapping ‒ QlikView‌

and ApplyMap ‒ QlikView‌

In my view try to identify errors in the words automatically is not very reasonable. If two words differ by one or more letters and have different meanings - is not always clear, it is a mistake or not.

Regards,

Andrey

Re: Group by a column based on fuzzy match

Anonymous — Sat, 25 Feb 2017 15:38:21 GMT

I think . Unless you have a solid lookup table, we cant always trust the numbers you get. Its hard to find out each case where data anomaly occurs and use some function to group it.

Re: Group by a column based on fuzzy match

ahaahaaha — Sat, 25 Feb 2017 16:38:36 GMT

You still want to automatically link

BOTICELLI

BOTTICELLI Inc

BOTICELI Brands in a single value?

Re: Group by a column based on fuzzy match

umashankarus — Sat, 25 Feb 2017 20:15:05 GMT

Yes, I would like to link in a single value

Thanks

Re: Group by a column based on fuzzy match

MarcoWedel — Sun, 26 Feb 2017 00:18:22 GMT

Hi,

maybe this helps:

Sorting similar names for display

Fuzzy Matching/Joining

Levenshtein Algorithm

Re: Fuzzy/Close Name Match

regards

Marco

Re: Group by a column based on fuzzy match

ahaahaaha — Sun, 26 Feb 2017 05:40:05 GMT

Excellent collection of links on the subject provided Marco below . I do not know the content of your task as a whole. In my opinion only the following should be considered.

Any language is redundant. Restore the meaning of the text by fragments of words and even individual words possible.

However, when it comes to the key fields, it seems to me, the use of fuzzy generalization method is not correct.

Regards,

Andrey

Re: Group by a column based on fuzzy match

umashankarus — Sun, 26 Feb 2017 06:21:16 GMT

Thanks Marco

Re: Group by a column based on fuzzy match

umashankarus — Sun, 26 Feb 2017 06:21:41 GMT

Thanks Andrey

Re: Group by a column based on fuzzy match

MarcoWedel — Sun, 26 Feb 2017 17:56:35 GMT

you're welcome

regards

Marco

Qlik Community Tip: Marking Replies as Correct or Helpful