String Functions: Part 1

In this series, I will be exploring string functions. QlikView has an abundance of functions available at your disposal to manipulate and parse what is presented in the final data model. This series will be focusing on string functions used during the load process.

The series will explore the following string functions and their uses

• Left(), Right() and Mid()
• Index()
• Subfield()
• KeepChar() and PurgeChar()
• FirstSortedValue() and Min()
• TextBetween()
• Replace()
• FindOneOf()
• SubStringCount()

Part 1: Left(), Right(), Mid() and Index()

I am confident that every QlikView developer has used the above function in some way or another. Firstly, lets explore their use in a non-nested way

Left/Right(Text,Count) - Takes the input text whether a literal string or an expression and from the left or right side, returns the characters for that length. There are many situations where this is useful. One common use is when a field contains a value that can be described as the sub type and you want to extract this out

Even though the functions Replace() and PurgeChar() will be discussed in more detail in a later series; I wanted to illustrate how they can be used in conjunction with Left()/Right() to display certain values. The user can now select an ID category and then an ID number to further drill down and isolate

Mid()/Index() are very powerful functions, especially when used together. Mid(Text,Start,Count) is a 3 parameter function. The difference to left and mid is that you can specify where to start from. This adds a nice dynamic when used in conjunction with Index. Index(Text,Substring,Count) returns the starting position of a string within a string. Lets look at the following example which will illustrate how these 2 functions operate

Once again, for illustration I have used a function to be discussed later i.e. FindOneOf(). But as before, this is to demonstrate the versatility of the mid function in conjunction with any other string parsing expression.

My expression Mid(ID,Index(Upper(ID),'H',1),3) as HATSubType returned the value HAT for whenever that instance occurred. I used Upper() to ensure that the string I was matching to would be found, this is because the string to match is case sensitive. The output to the load above is

Essentially, 1 of 2 things need to be present when applying transformation to your data.

1. Does a pattern or set sequence exist - In the above examples, the subtype was always 3 characters long. Where it started did not matter now, what did was finding it. Since I knew the preceding character would be numeric, it was easy to find the starting position of my ID SubType
2. Is there a common delimiter - In the following example '011-254-7398', there is a '-' between each part in the above phone number. Since this will occur for all phone numbers, we can use Index(PhoneNumber,'-',which instance to use) to isolate the starting position of the characters we do want

I hope you enjoyed this part of the series and if you have any suggestions on what I should discuss next, please let me know

Until next time, cheers!