QlikView offers multiple substring functions that are useful when you have to transform data.
That said, I was recently asked to find a way to add description from a table which contains substring of the key field.
I was able to accomplish this task by using two string functions: Mapsubstring and Purgechar.
Back to Basics
Let’s explore definition of both functions first.
Mapsubstring function:
The mapsubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:
Mapsubstring(‘mapname’, expr)
This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement (see Mapping). The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.
Purgechar function:
Purgechar(s1, s2)
Returns the string s1 less all characters contained in string s2.
Example: purgechar( ‘a1b2c3′,’123’ ) returns ‘abc‘
With these two useful QlikView string functions under our belt, let’s walk through steps to put them in practice.
1. Create a mapping table.
If you are new to QlikView and not sure what mapping table does, you may want to read this article.
Map_1:
Mapping LOAD * INLINE [
Dim, Diagnosis
1234, Suspected Appendicitis
567, Eating Disorder
]
;
2. Use both mapsubstring and purgechar functions
First, mapsubstring will map keyfield from the fact table which contains a substring from the keyfield of the mapping table.
Next, purgechar will remove additional characters so that description can be added to the fact table.
Transactions:
Load *,
purgechar(MapSubString(‘Map_1’, Dim1), Dim1) as Diagnosis
;
LOAD * INLINE [
Dim1
123456abCD
]
;
