topic Re: String functions in QlikView

String functions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have a certain data value of which I need to exclude a certain portion of it.

The data is as follows: 1 h 5 m 4 s 841 ms

I need to exclude the '841 ms' value from the string. What approach/functions can be used?

Thanks and regards,

Erwin

Re: String functions

sunny_talwar — Thu, 22 Jun 2017 13:22:47 GMT

May be like this

=Left('1 h 5 m 4 s 841 ms', Index('1 h 5 m 4 s 841 ms', ' ', -2)-1)

or this using a field

=Left(FieldName, Index(FieldName, ' ', -2)-1)

Re: String functions

henrikalmen — Thu, 22 Jun 2017 13:25:06 GMT

One way could be:

left([theField], index([theField], 's'))

That expression finds the position of the first letter 's' in theField, and then returns only the leftmost part of the string up until (and including) that 's'.

Of course in this approach there must be an 's' for seconds in your string, otherwise the function would find the 's' in 'ms' and you don't want that...

Re: String functions

Anonymous — Thu, 22 Jun 2017 13:36:44 GMT

Hi Henrik, is there no way that I identify the keyword ms in the data and then exclude it. If the milli second value is not present, then there is no need for a substring.

Re: String functions

henrikalmen — Thu, 22 Jun 2017 13:40:19 GMT

Sure, use IF, somthing like this:

if(index([theField], 'ms')>0, 'ms exists', 'ms doesn't exist')

Replace my string values with expression of your choice.

Re: String functions

Peter_Cammaert — Thu, 22 Jun 2017 13:43:38 GMT

Something like this?

IF (right(StringField, 2) = 'ms', left(StringField, index(StringField, ' ', -2)-1), StringField)

Re: String functions

Anonymous — Thu, 22 Jun 2017 13:58:16 GMT

Thank you Peter, it works perfect.