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
]
;

mapping