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.
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:
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.
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.
Mapping LOAD * INLINE [
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.
purgechar(MapSubString(‘Map_1’, Dim1), Dim1) as Diagnosis
LOAD * INLINE [