Qlik String functions are an extremely handy set of functions to clean, manipulate, and ultimately normalize data before (or after) loading it into an app. String functions ‒ Qlik Sense

 

I was working with a new data set recently, among other fields the data set contains addresses, something like this:

 

Abbey Road, London

 

Since the data set doesn’t have a dedicated city field I decided to apply Subfield() to extract the City as a separate field. Easy task, right? I use the comma as a separator to split the string.

 

…
Subfield (address, ',', 2) as City,
…








 

Shortly after we decided to expand the data universe of the app to include other countries in our analysis, we ended up having multiple sources with slightly different address formats:

 

Abbey Road, London

Paseo del Prado, s/n , Madrid

91, Rue de Rivoli, 75001, Paris

 

Not great news, right? Due to the variable number of commas in the address field I can no longer use subfield to manipulate the string, or maybe I still can use it.

 

Searching from the end of the string

 

Subfield function not only takes positive values for the third parameter, it also accepts negatives values. A negative value searches from the end of the string. This will help us to extract the City while keeping my script highly readable. The new function will be something like:

 

Subfield (address, ',', -1)








 

The function above will return the first portion of the string separated by the delimiter comma (',') searching from the end of the string, right to left. The newly created "City" field contains:

 

     London

     Madrid

     Paris

 

Right to left search in String functions


The use of negative values, or right to left search (remember the read will still be performed from the start of the string) also apply to other string functions:


  • Subfield

    Subfield('Abbey Road, London', 'o',-2) 

    Returns 'nd' (the second occurrence of 'o' starting from the end)

  • Index

    Index('Abbey Road, London', 'o',-2) 

    Returns 14 (the second occurrence of 'o' starting from the end)

  • FindOneOf

    FindOneOf('Abbey Road, London', 'oes',-3) 

    Returns ‘8’ because the search is for any of the characters: o, e or s, and "o" is the third occurrence starting from the right, and is in position 8 starting from the left.

 

I hope you find it as useful as I do.

AMZ @arturoqv