topic Re: Alternative String Functions to Replace in QlikView

Alternative String Functions to Replace

shane_spencer — Wed, 02 Aug 2017 07:34:11 GMT

Hi All - just wanted your suggestions for best /neatest code. I've got some data and sometimes the contents of one of the fields is preceded by 1. 2. or 3. Obviously I can't use PurgeChar as it's a string of 2 characters plus there's numbers in the data. I opted for using a replace - or more accurately 3 replaces (see below) - but I think there may be a more efficient way (especially if I had more strings to replace than those 3). I did think of an If statement combined with mid but that didn't seem any more efficient that what I'd got. Any better ideas?

TRIM( Replace( Replace( Replace(FIELDNAME,'1.','') ,'2.','') ,'3.','') ) As NEWFIELDNAME,

Re: Alternative String Functions to Replace

tresB — Wed, 02 Aug 2017 07:40:41 GMT

May be using subfield(), considering dot (.) a separator, like:

Load

SubField(Field, '.', 2) as NewField

Re: Alternative String Functions to Replace

shane_spencer — Wed, 02 Aug 2017 07:48:07 GMT

That would work in this case because there are no other dots (.) in the data, but it doesn't seem very robust. Thanks though.

Re: Alternative String Functions to Replace

tresB — Wed, 02 Aug 2017 07:54:35 GMT

This?

Load

Replace(Field, SubField(Field, '.', 1)&'.' , '') as NewField

Re: Alternative String Functions to Replace

antoniotiman — Wed, 02 Aug 2017 07:55:12 GMT

Hi Shane,

You can use MapSubstring() Function

like this

MapTable:
Mapping LOAD * Inline [
A,B
1.,
2.,
3.,
];

LOAD *,MapSubString('MapTable',Field) as MappedField Inline [
Field
1.00A
2.00B
3.00C
400
500
600];

Regards,

Antonio

Re: Alternative String Functions to Replace

shane_spencer — Wed, 02 Aug 2017 07:58:00 GMT

That's clever - I haven't used MapSubstring() function before.

Re: Alternative String Functions to Replace

ahaahaaha — Wed, 02 Aug 2017 07:59:12 GMT

Hi,

May be as variant

LOAD*,

If(IsNull(Left(Field, Index(Field, '.')-1))=0, Right(Field, Len(Field)-Index(Field, '.')), Field) as Newfield;

LOAD*Inline

[Field

1.fjfk

22.jjfkfl

3.345

44.3e4r

jfkgl

jdjdk

2354.5hytu

876.ryt5

];

Result

Regards,

Andrey

Re: Alternative String Functions to Replace

shane_spencer — Wed, 02 Aug 2017 08:00:19 GMT

That's very neat - as long as there's no other dots in my data to screw things up. Thanks!

Re: Alternative String Functions to Replace

shane_spencer — Wed, 02 Aug 2017 08:01:41 GMT

Interesting - very useful if not easy to read. Thanks.

Re: Alternative String Functions to Replace

tresB — Wed, 02 Aug 2017 08:06:49 GMT

If you want to cut short from the first dot, you could try:

=Mid(Field, Index(Field,'.'))

Re: Alternative String Functions to Replace

ahaahaaha — Wed, 02 Aug 2017 08:21:47 GMT

Function Index() determine the location of the first point.

If(IsNull(Left(Field, Index(Field, '.')-1))=0, Right(Field, Len(Field)-Index(Field, '.')), Field) as Newfield

We check if there is a number on the left,

If(IsNull(Left(Field, Index(Field, '.')-1))=0, Right(Field, Len(Field)-Index(Field, '.')), Field) as Newfield

then discard it,

If(IsNull(Left(Field, Index(Field, '.')-1))=0, Right(Field, Len(Field)-Index(Field, '.')), Field) as Newfield

otherwise we take the whole field.

If(IsNull(Left(Field, Index(Field, '.')-1))=0, Right(Field, Len(Field)-Index(Field, '.')), Field) as Newfield

I'm glad if I help.