topic Re: Creating a wild Match search in QlikView

Creating a wild Match search

bimanbeginner — Tue, 21 Jun 2016 11:26:09 GMT

Hi There

Please could you assist in making my wild match search as dynamic as possible

I need to match column Name1 to Name2 if there is any matching words it must return true.

In my example all of them should return true im not sure what I am doing wrong.

Thanks

Re: Creating a wild Match search

giakoum — Tue, 21 Jun 2016 11:46:53 GMT

It looks perfectly correct to me as only "Bubble Gum" exists in both name1 and name2

What exactly are you after?

Re: Creating a wild Match search

bimanbeginner — Tue, 21 Jun 2016 11:54:48 GMT

All of them them contain a word from the Name1 residing in Name2

I would like to all of them to say true...because they contain at least one word from Name1

Re: Creating a wild Match search

bimanbeginner — Tue, 21 Jun 2016 12:34:02 GMT

All of them them contain a word from the Name1 residing in Name2

I would like to all of them to say true...because they contain at least one word from Name1

Re: Creating a wild Match search

ciaran_mcgowan — Tue, 21 Jun 2016 13:16:46 GMT

Hi bimanbeginner,

I see what you're trying to do here. The first thing you need to do is separate out all the potential words from Name1. In the load script, add this line:

Subfield(Name1, ' ') AS Name1_Words

This will create a row of data for each word which means there will be duplication that may need to be removed later. Once the data is separated you can accomplish what you want with the script below:

if(WildMatch(Name2, '*' & Name1_Words & '*'),'True','False')

Job Done!

Re: Creating a wild Match search

trdandamudi — Tue, 21 Jun 2016 13:26:24 GMT

What Ciaran mentioned above is the best option or if you think you have only couple of words then you can use the below expression:

=if(WildMatch(Name1,'*'&Name2&'*') or WildMatch(Name2,'*'&Name1&'*')

or WildMatch(Name1,'*' &subfield(Name2, ' ' ,1)&'*') or WildMatch(Name1,'*' &subfield(Name2, ' ' ,2)&'*') ,'True','False')

Re: Creating a wild Match search

ciaran_mcgowan — Tue, 21 Jun 2016 15:58:22 GMT

I suggest that you have a look at my example below, it works. I even added an extra field to test that it does.

Re: Creating a wild Match search

bimanbeginner — Wed, 22 Jun 2016 07:28:07 GMT

Thank you , I guess this does answer my question although it will not work in my actual case as the Name1 field is made up of many different combinations of delimiters

Re: Creating a wild Match search

ciaran_mcgowan — Wed, 22 Jun 2016 08:40:19 GMT

Can you post an example of some of your data?

I've had to do a lot of data clean up to get this to work sometimes (especially when scraping from the web) so I still might be able to help.

Re: Creating a wild Match search

bimanbeginner — Thu, 23 Jun 2016 04:54:28 GMT

Thank you Ciaran I appreciate it.

The root cause of my problem, is that I need to compare a bank statement to system transactions.

As you know with bank statements a client could put any reference when making a payment.

I need to link the payment on the statement to the transaction to show the reference the client has used.

The wild match I am using is to find any reference that may contain the clients name or a part thereof to create a new mapping table to try and Identify the new payments in the future... Maybe there is a better way to do this...?

Mapping Data‌