Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Connect 2026! Turn data into bold moves, April 13 -15: Learn More!
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
ahaahaaha
Partner - Master
Partner - Master

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.