I have some Data as follows:
I am trying to tidy the data to resemble
By using PurgChar it removes all The X,D & A's which is not my desired result.
Can anyone suggest a successful way of doing this?
It depends on the rule you are applying.
If you have a concrete small number of conversions, i would do by if statement.
If you are able to think in a concrete rule, then let's see...
Otherwise you can load a conversion table with input and output fields, and join it after load your data.
Left(Field,1) & PurgeChar(Trim(SubField(Field,' ',1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as NewField
You want to remove the non-numerics from the right side, correct? I think Manish was on the right track but I would code it as
=left(Field,1) & KeepChar(mid(Field,2),'0123456789')
Try like this
Load *, If(IsNum(Right(Field, 1)), Field, Trim(Mid(Field, 1, Len(Field)-1))) as Result Inline
LOAD * INLINE [ TempData X123 A09X K85X A12 D B56 A ]; load TempData, right(TempData,1) as testTemp, if(WildMatch(right(TempData,1),'1','2','3','4','5','6','7','8','9','0'),TempData,Mid(TempData,1,LEN(TempData)-1)) as new_data resident Temp; DROP Table Temp;
I have ended up with a function within the extracting database to trim off the various characters before extraction. All you help was much appreciated.
Retrieving data ...