topic Re: Remove characters at start of string in QlikView

Remove characters at start of string

quilldew — Wed, 18 Apr 2018 10:01:17 GMT

Hi all,

I know this is a popular one, and I've read many threads about string functions but I can't seem to put one together to fit this particular need.

I wish to remove any character before a name. This name is preceded by numbers and spaces and a hyphen, and sometimes another separator (usually a dot). Examples are:

1 - Name

12 - Name

23 - Name

3.1 - Name

2- Name

2-Name

Name

2.5 - Name - SecondPartofName

I've tried various combinations of Trim(), Mid(), Left(), PurgeChar() but don't seem to be able to just keep the name.

I've considered using if()'s but I'm trying to keep the script fast as I'll be using this on lots of imported fields.

Any ideas?

Many thanks.

Re: Remove characters at start of string

PrashantSangle — Wed, 18 Apr 2018 10:03:58 GMT

use trim(subfield(fieldName,'-',2))

Regards

Re: Remove characters at start of string

stigchel — Wed, 18 Apr 2018 10:06:15 GMT

Try with

Trim(SubField(YourField,'-',2)) as Name

Re: Remove characters at start of string

quilldew — Wed, 18 Apr 2018 10:54:15 GMT

Thanks for the suggestion,

Unfortunately the Name sometimes also has a hyphen within the name. This gets rid of everything after the second hyphen too.

Re: Remove characters at start of string

sunny_talwar — Wed, 18 Apr 2018 11:02:44 GMT

May be this

Trim(Mid(FieldName, Index(FieldName, '-') + 1)) as Name

Re: Remove characters at start of string

sunny_talwar — Wed, 18 Apr 2018 11:05:13 GMT

Sample script

Table:

LOAD *,

Trim(Mid(FieldName, Index(FieldName, '-') + 1)) as Name;

LOAD * INLINE [

FieldName

1 - Name

12 - Name

23 - Name

3.1 - Name

2- Name

2-Name

Name

2.5 - Name - SecondPartofName

];

Which give me this

Re: Remove characters at start of string

quilldew — Wed, 18 Apr 2018 13:11:53 GMT

Sunny!

I wondered how long it would take you

Thank you very much again that worked perfectly.