topic Fuzzy linking in QlikView

Fuzzy linking

Mon, 13 Sep 2010 15:42:40 GMT

Hi,

I have a rather odd problem. I want to categorize the bookings on my Bank account statement.
in order to do that I have three description fields for every statement.

The problem is that :

The identifier on which to categorize a statement might be in either one of the description fields
Within the description there might be other junk that I want to ignore while matching.

So as an example a booking might look like this:

Date: 1.1.2010
Amount: 100
Description 1: Debit Card
Description 2: Thx for shopping with us
Description 3: Your TOYS R US team on the 1.1.2010

For the problem that the identifier might be in all of those 3 fields there is an easy solution, concatenate them, so you have one big string to search in.
So now I want to go through that string and dependent on what I find set the Category on that booking for eg. Toys.

I know I could do a lot of IF clauses that do a LIKE search on this string but what I'm thinking of is a table that holds all the wildcard searches and the associated category.

E.g. :

Search_Pattern Category

'*Toys R US*' 'Toys'
'*BMW*' 'Car'
.....

So when the description string is evaluated it should go through that list and search for that pattern first match and the category should be set/returned.

I know that you can define functions in QLK but not really sure how to do that or if its even possible?

Cheers,

Flo

Fuzzy linking

johnw — Mon, 13 Sep 2010 22:58:31 GMT

As long as the number of patterns is relatively limited, there's a fairly clever way that Rob Wunderlich came up with. (I came up with it independently, but then found out he'd already done it and posted it, so he gets the credit. ;)) Assuming you can open other people's files, download his QlikView Cookbook:

http://robwunderlich.com/Download.html

The example you're looking for is mappingwithwildcards.qvw. If you're unable to open other people's files, let me know, and I can copy in the script. If you're having trouble understanding how it works, I can explain further, but there are a lot of comments in the script and on the screen.

Fuzzy linking

Tue, 14 Sep 2010 07:37:28 GMT

Hi,

On a first read that sounds like exactly what I'm looking for, will check it out right now.
And I'm able to open other people's files, thx for your help !

Cheers,

Flo