topic Re: List box set analysis with wildmatch in QlikView

List box set analysis with wildmatch

Wed, 30 Nov 2011 20:24:52 GMT

I would like to display in a list box, an imported account list that matches our customer names inside of the existing dashboard. This has to be a wildmatch. Less than 1% of the account name spellings match our customer name spellings. If this cannot be done in a list box, can this be done in a where clause?

Re: List box set analysis with wildmatch

Miguel_Angel_Baeyens — Wed, 30 Nov 2011 20:46:30 GMT

Hi Dan,

Although it will do your load script real slow, this might give you an idea:

Table:
LOAD *
FROM File.xls
WHERE WildMatch(Name, '*John*', '*Peter*', '*Mark*', '*Paul*', 'Mic?ael');

Using a mapping table and the ApplyMap() function in the WHERE clause might do as well:

NamesToLoadMap:
MAPPING LOAD * INLINE [
Name, OK
John Peters, 1
Mark Walls, 1
Michael Strong, 1
];
Table:
LOAD *
FROM File.xls
WHERE ApplyMap('NamesToLoadMap', Name, '$$') = 1;

Hope that helps.

Miguel

List box set analysis with wildmatch

Thu, 01 Dec 2011 15:12:09 GMT

Miguel, Thanks for the response. The wildmatch would work, but requires a lot of work. The applymap would only work for the names that are spelled the same, correct? I was looking for a way to do something like a where exists.

Re: List box set analysis with wildmatch

Miguel_Angel_Baeyens — Thu, 01 Dec 2011 19:40:54 GMT

Dan,

That's right, mapping tables will work only for values that are spelled the same, and yes, WildMatch() means a lot of hardcoding. EXISTS() is available in QlikView, but has a different meaning (only values previously loaded). There's nothing like "SOUNDEX" or similar in QlikView.

Hope that helps.

Miguel

Re: List box set analysis with wildmatch

rwunderlich — Fri, 02 Dec 2011 01:38:03 GMT

Dan,

If you decide to try the WildMatch approach, it may be easier with a table driven aproach like in the attached example.

I think it would be relatively easy to code up a soundex function in the vb module that you could call from the script.

-Rob