String Functions

[Jennell_Yorkman]

QlikView is full of functions that can be used in expressions and/or the script to manipulate and parse the data.  I decided to write about some common string functions that can be very helpful:

1. Subfield()
2. Len()
3. Index()
4. MinString()
5. MaxString()
6. Substringcount()

Subfield()

One of the most useful functions is the Subfield() function.  From within the script, this function returns a specific substring from a larger delimited substring.  This function allows you to transform a table like this:

To a table like this parsing the Color field so that each product and color combination has its own row.  This makes it easier to filter the data by color using list boxes.

Len()

The Len() function returns the length of a string in either an expression (Len(Name)) or in the script providing the number  of characters in in string like this:

Index()

The Index() function returns the position of a substring within another string.  This function may be overlooked but it can be very handy when parsing a string field like a phone number.  Using the Index() function, I can determine where the ‘-‘ are in the phone number therefore capturing the parts correctly.

  Phone:
NoConcatenate LOAD
ID,
Phone,
Left(Phone, Index(Phone, '-')-1) as Phone1,
Mid(Phone, Index(Phone, '-')+1, 3) as Phone2,
Right(Phone, Len(Phone)-Index(Phone, '-', 2)) as Phone3
Resident PhoneTemp;

MinString() and MaxString()

Next are the MinString() and MaxString() functions.  I think everyone has used these functions at least once in QlikView.  They return the first or last value over a dimension (in an expression) or over a group by clause (in a script).  I find these functions most helpful in chart expressions when I need to see the first or last value across a dimension.

Substringcount()

The last function is the Substringcount() function.  I think this is a hidden treasure – not many people know about it but once you do, you use it all the time.  The Substringcount() function returns the number of times a substring is in a string.  So, if the field String is “abcdefabcdef” then the expression Substringcount(String, ‘def’) will return 2.  I find this helpful with a show condition when I want to determine is a value has been selected or is possible.

There are so many string functions in QlikView that can make your life easier.  Browse through them the next time you are in the Help section to see what shortcuts you can use to manipulate your data.

I wrote a technical brief about these string functions with more detail.

[danielrozental]

textbetween is probably the best string function in QlikView for extracting text from a string.

Mapsubstring is also very good for cleaning or replacing values within a string or a field.

[blaise]

And not to forget, the mid() function. It is used in the index() explanation above and one of the string functions i use most (and mostly in combination with either substringcount() or len().

[quiquehm]

Nice technical brief Jennell !

I have been diving in the qlik community for some time now trying to find how I could do a date field data cleanup, but I couldn´t find anything yet. Hope you guys can give me some guidance. It´s all about replicating a LIKE function from SQL. I have a date field with multiple date formats , like below example :

date_field

m/dd/yyyy

dd/mm/yyyy

yyyy-mm-dd

dd.mm.yyyy

and I would need to convert all these formats to a consistent format, for example yyyymmdd, so I can extract year, month, and aggregate data as needed. I found a way to do it in an ETL forum, where a LIKE function is used in a WHERE clause in the following way : ( for example to extract format m/dd/yyyy )

Select

date_field

from

table

WHERE date_field <>'' AND date_field LIKE '[0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%'

then a REPLACE function would be added per different type of date format to convert.

Do you know "the qlikview way" to perform this type of data cleansing in a date field ?

The whole article on the ETL code is here :

string manipulation in ms sql|working with LIKE and PATINDEX ms sql

I will appreciate any guidance you can offer

Thanks a lot

[blaise]

Date(alt(date#(date_field,'M/DD/YYYY'),date#(date_field,'DD.MM.YYYY'))).

you can include as many date# in the alt() function as you like.

[quiquehm]

Thank you Johan ! I just tested it and of course it works !. There is only one challenge ( but I am afraid nothing that can be solved with Qlikview  ), and it is when you have the format M/D/YYYY versus the format D/M/YYYY, for particular values when both D and M are less than 12 like for example 1/3/2014 ..as it comes as a text from the source system you don´t really know if it is March 1st or Jan 3rd . I noticed Qlikview follows the order of formats you write in the script, left to right ( as an IF statement ), which makes sense

Date(alt(date#(datetextfield,'M/D/YYYY'),date#(datetextfield,'D/M/YYYY')   )) as datefield_clean,

I also tried MM/DD instead of M/D and apparently it does not make any difference, do you know if both are the same ?

Again, many thanks for the help

Enrique

[#7]

Thanks for uploading the stuff, it was very handy.

It saved a lot of effort