Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
May be using subfield(), considering dot (.) a separator, like:
Load
SubField(Field, '.', 2) as NewField
That would work in this case because there are no other dots (.) in the data, but it doesn't seem very robust. Thanks though.
This?
Load
Replace(Field, SubField(Field, '.', 1)&'.' , '') as NewField
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
That's clever - I haven't used MapSubstring() function before.
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
That's very neat - as long as there's no other dots in my data to screw things up. Thanks!
Interesting - very useful if not easy to read. Thanks.
If you want to cut short from the first dot, you could try:
=Mid(Field, Index(Field,'.'))