CODEWITHSUNDEEP
qlikviewqliksense
Matt
Matt

Reputation: 15061

Qlikview Substring and charindex equivalent to show values after the - character

I have a field which has the values

field
good - examplea
good - exampleb
bad - examplep
ugly - examplet
ugly - exampley

I want to only show values after the - character.

My example output would be

field
examplea
exampleb
examplep
examplet
exampley

In SQL it would be simply

SUBSTRING('ugly - exampley',CHARINDEX('- ', 'ugly - exampley', 1)+2,250)

AND

SUBSTRING(field,CHARINDEX('- ', field, 1)+2,250)

What is the equivelant in Qlikview

Upvotes: 1

Views: 7395

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

You can either use mid (with index) or subfield as follows:

Mid & Index

The equivalent of your statement would be:

mid(field, index(field,'- ', 1) + 2, 250)

Here, mid is the equivalent of SUBSTRING and index equivalent of CHARINDEX. However, in QlikView, mid's third parameter (number of characters to return) is optional, so you could instead use 

mid(field, index(field,'- ', 1) + 2)

which would return the remainder of the field value after the -.

Subfield

Subfield allows you to delimit your input string with another string and then return a specific delimited substring. In your case, the below would do the trick:

subfield(field, ' - ' , 2)

For example, for the string good - examplea, this breaks it down by looking for the delimiter -. This results in two strings, good and examplea. The last parameter, 2, tells subfield to return examplea (rather than good which could be obtained by using 1 as the third parameter).

The good thing about subfield in your case is that you do not need to specify how many characters to return as subfield will return all characters to the end of the string.

Upvotes: 5

Related Questions