Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Alternative String Functions to Replace

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,

10 Replies
tresesco
MVP
MVP

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

Load

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

shane_spencer
Specialist
Specialist
Author

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

tresesco
MVP
MVP

This?

Load

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

antoniotiman
Master III
Master III

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

shane_spencer
Specialist
Specialist
Author

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

ahaahaaha
Partner - Master
Partner - Master

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

1.jpg

Regards,

Andrey

shane_spencer
Specialist
Specialist
Author

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

shane_spencer
Specialist
Specialist
Author

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

tresesco
MVP
MVP

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

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