Use case
A gas company wants to quickly generate estimates for prospective customers using data about what they charge similar, current customers. The most important variable they want to account for is the location of the prospect, i.e., what are we charging existing customers in the same delivery area?
This works when there are customers in the prospect’s area, but what if the prospect would be the first customer in the vicinity, with no cohort? In those cases, we want to widen the geographic area to look for current customer prices there. If there are still no customers nearby, we “zoom out” to widen the scope again until we find a cohort on which to base the quote. If no matches are ever found, we will default to an overall national average price. What we are building is a prioritized list of potential matches, and we want to use the best match supported by our data.
Implementing “best match” in Qlik
ApplyMap('map_name', expression [ , default_mapping ] )
Qlik supports this use case well because of the ApplyMap function’s optional third parameter: a default value to assign if no match is found in the lookup. Nesting another ApplyMap in the third parameter of an ApplyMap gives Qlik another lookup to try if no matches are found in the first. Here is a simple example, but it could be expanded with more lookups, as needed.
ApplyMap('MapIdealMatch', LookupValue1,
ApplyMap('MapNextBestMatch', LookupValue2,
DefaultValue))
The pattern is that each attempted lookup requires one Mapping table and one corresponding ApplyMap function in the expression.
Steps to solve
As an example, here is the solution to the hypothetical gas company problem above.
Build Mapping tables
Perform lookups
Result
Variation of solution, with breadcrumbs
If you want to include metadata about which specific lookups are matching, i.e., the basis of the quote prices, this can be done with a modification to the Mapping tables. We can “tag” the lookup expression results by concatenating each Mapping table description directly to beginning of the returned values. Note in the Mapping table code below the addition of text like “ZIP|” and “County|” to the beginning of the Average Price Amount expressions. (The vertical bar will be our delimiter.) This is also helpful for debugging, because you may find that one level of the lookup is getting no matches at all, and you need to check your work.
Because the lookups return concatenated values, like “ZIP|12345…”, an additional step is required to split the lookup value into in separate fields, using SubField. Below I included screenshots before and after that parsing, so there is a visual of what is returned by the ApplyMap.
Build Mapping tables
Perform lookups
Result before splitting concatenated value into two fields — note concatenated Quote Price Match field
Final result — separate Match Type and Amount fields
Other best match examples
Above is a simple example for demonstration, but you may encounter others.
Let’s start with a variation on the original use case. Increasing complexity may entail creating a hierarchy of potential matches based on multiple properties at the same time. Here’s an example of how you might build on the geography-only use case, incorporating Customer Type as another possible cohort characteristic (in order of preference):
- 1. ZIP and customer type
- 2. County and customer type
- 3. Just ZIP
- 4. Just County
- 5. Just State
- (Default to National)
The “and” scenarios above (1 and 2) require creating a concatenated key in the Mapping table lookup value, like [ZIP Code] & ‘|’ & [Customer Type], as was done with State and County in the original solution.
The first time I used this approach was for a sporting goods manufacturer who wanted to determine whether the people who registered at their expo booth were prospects or existing customers by trying to match different bits of information they collected to fields in their CRM system, like email domain, name, and phone number. Another more common example may be to automatically choose to the best data source available when the same information could be found in many tables or source systems, and you have a preference based on the quality of each.
When you see the final recipe and solution, it is pretty simple, but my hope is that it will save some iterations when you encounter this class of problem.
