Wild Thing I'll Map Your Pattern Strings

While working on a QlikView project I was presented with an troublesome problem where I was trying to map some codes to a set of descriptions but the mapping was based on a pattern string. So I have a list that looks something like this…

Department Pattern	Description
ORT*	Orthopedics
VAS*	Heart & Vascular
CATH*	Heart & Vascular
*FAM*	Family Services

This is a highly abbreviated list but I think you’ll get the idea. I went looking for a mapping function that would help me match the pattern strings and I found two mapping functions ApplyMap() and MapSubstring(). I know ApplyMap won’t help me because it does an exact match and MapSubstring does not really do the job either because it will match a partial string but not a pattern.

The function that I decided to explore was WildMatch(). WildMatch is an interesting function, it requires two arguments, the first is a string usually a field that you would like to examine and then a list of pattern strings to search.

Load
WildMatch([DeptCode], ‘ORT*’,’VAS*’,’CATH*’, ‘*FAM*’) as [Department Pattern List Number]
Resident
Departments;

The WildMatch function returns a number that matches the number of the argument where the match was found and zero when there is no match. I thought if I used this function with a carefully prepared mapping table then I could achieve the “WildMap” functionality.

Step 1. Load the map as a regular table and add a row number field to the table.

DeptPatterns:
Load
RowNo() as RowNumber,
DeptPattern,
DeptDesc;
Load * Inline [
DeptPattern, DeptDesc
ORT*, Orthopedics
VAS*, Heart & Vascular
CATH*, Heart & Vascular
*FAM*, Family Services
];

Step 2. Concatenate the patterns into a string that will serve as the arguments for the wildmatch function. Notice that I am using the sort weight parameter in the concat function to ensure the list items are in the proper order. I am creating a simple table assigning the field value to a variable and dropping the table

PatternStrings:
Load
Concat( chr(39) & DeptPattern & Chr(39),',',RowNumber) as PatternList
Resident
DeptPatterns;

Let vDeptPatternList = FieldValue('PatternList', 1);

Drop Table PatternStrings;

Step 3. Create the mapping table.

DeptMap:
Mapping
Load
RowNumber as Arg,
DeptDesc as Desc
Resident
DeptPatterns

Order By RowNumber;

Step 4. Use the result of the WildMatch function to determine what row in the mapping table has the desired description.

Load
ApplyMap('DeptMap',WildMatch([DeptCode], $(vDeptPatternList)),'Not Found') as [Department Group],

…
Resident
Departments;

In conclusion, I found this technique helpful to meet this requirement I hope you will find it useful too.