topic Finding a string in QlikView

Finding a string

Tue, 23 Nov 2010 23:24:55 GMT

I'm hoping someone can point me in the right direction. I have a list of names that I need to compare to another list of names. Within the script, I would like to grab one name, do some cleanup on it, and see if that name exists in my other table. Would I need to do this using a select function? Or is there another function that can be used to search for a string value within a table?

Also, does QlikView support any kind of fuzzy matching on a string?

Thanks in advance for your help!

Finding a string

johnw — Tue, 23 Nov 2010 23:41:09 GMT

Sounds like the exists() function?

QlikView isn't so good at fuzzy matching. You could look at the wildmatch() function, but I doubt that's what you're looking for here.

Finding a string

Wed, 24 Nov 2010 02:45:09 GMT

I'd give applymap() a try for that.

Finding a string

johnw — Wed, 24 Nov 2010 17:55:24 GMT


dragonauta wrote:I'd give applymap() a try for that.

Good idea, or what I usually use, mapsubstring(capitalize()). It's a good way to resolve minor name differences if you have few enough names that you can look for and resolve problems manually (and I'm not sure how much I'd trust a computer to do it anyway).

Here's a simple example from one of my applications.

[Text Map]:
MAPPING LOAD * INLINE [
Replace This, With This
Anthony Proznick, Tony Proznick
Daniel Sutton, Dan Sutton
Douglas Johnson, Doug Johnson
Gerald Sifferman, Gerry Sifferman
Elizabeth Bunosky, Liz Bunosky
Human Resources, HR
Kameron Davis, Kami Davis
Kijeong Yi, Kory Yi
Marilyn Lindberg, Beth Lindberg
Mccarthy, McCarthy
Mckee, McKee
Mcmahon, McMahon
New Request Submitted, New
Paul Obermire, Michael Obermire
Randall Thompson, Randy Thompson
Rebecca Alvarez, Becky Alvarez
Robert Silva, Bob Silva
Salvatore Sbranti, Sal Sbranti
Thomas Blasingame, Tom Blasingame
WEB, Web
Willard Bunyan, Bill Bunyan
William Waters, Bill Waters
William Swenson, Bill Swenson
];

,if(len(trim(Name))
,mapsubstring('Text Map'
,capitalize(if(len(trim(subfield(subfield(Name,',',2),' ',1)))
,trim(subfield(subfield(Name,',',2),' ',1))
,trim( subfield(Name,',',2)))
& ' ' & trim(subfield(Name,',',1))))
,'NS') as "Requestor"