Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
What would be the right way to remove directly repeating values in a string?
e.g.
Source string is "123444566556789"
Target string should become "12345656789".
So, it's not really finding the unique values, but rather avoiding the same number being repeated multiple times after one other.
To give some background information: each number (1-9) represents a specific status, and I need to know the number of status changes.
Thanks for your assistance!
Kind regards,
Maarten
I think there are at least three different possible approaches. First is to use a quite large nested if-loop with checking of each char with string-functions. Second would be to split the string with a load and while len(string) <= iterno() and afterwards you applied a concat(distinct string) to put the pieces together again and the third solution would be to use How to use regular expressions.
- Marcus
I think there are at least three different possible approaches. First is to use a quite large nested if-loop with checking of each char with string-functions. Second would be to split the string with a load and while len(string) <= iterno() and afterwards you applied a concat(distinct string) to put the pieces together again and the third solution would be to use How to use regular expressions.
- Marcus
Hi,
You can try MapSubstring
map1:
mapping load * inline [
x, y
1, <one>
aa, XYZ
x, b ] ;
MapSubstring ('map1', 'A123') returns 'A<one>23'
MapSubstring ('map1', 'baaar') returns 'bXYZar'
MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'
Replace characters with Ascii numbers in a string
Re: how to remove some part of row data?
Hope this helps you.
Regards,
Jagan.
Try this sample script
NumMap:
Mapping LOAD
*
INLINE [
AcutalNum, ReplaceNum
444, 4
44, 4
66, 6
55, 5];
Data:
LOAD
*,
MapSubString('NumMap', Number) AS Clean_Num
INLINE [
Number
123444566556789];
Note: you can add as many numbers as you want in mapping table.
Regards,
Jagan.
my idea in the attachment
s:
load
rowno() as id,
str,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(str, '1', '1@'), '2', '2@'), '3', '3@'), '4', '4@'), '5', '5@'), '6', '6@'), '7', '7@'), '8', '8@'), '9', '9@'), '0', '0@') as str2
inline [
str
123444566556789
123444566
1234444445555556666666444444455555555555666666666644444445555555555666666666
];
left join (s)
load
id, AutoNumber(rowno(), id) as id2,
SubField(left(str2, len(str2)-1), '@') as c
Resident s;
left join (s)
load
id, id2,
if(Previous(id)=id and Previous(c)=c, 1, 0) as flagdupl
resident s
order by id, id2;
final:
NoConcatenate load
id, Concat(c, '', id2) as newstr
Resident s
Where not flagdupl
Group By id
Order by id, id2;
You can do it like this for an unkonwn number of possible repeatitions:
REMOVE_DUPS:
LOAD ID, only(Input) as Input, Concat(SubTask, '', TaskID) as Output
WHERE SubTask <> Previous(SubTask)
GROUP BY ID;
LOAD recno() as ID,
Input,
mid(Input, iterno(),1) as SubTask,
iterno() as TaskID
WHILE iterno() <= Len(Input);
LOAD * INLINE [
Input
123444566556789
];
Hi Maarten, another way to do the task:
Sub RemoveDuplicates(String)
Let vText='';
For i = 1 to Len(String)-1
If Mid(String, $(i), 1)<>Mid(String, $(i)+1, 1) then
LET vText = vText & Mid(String, $(i), 1);
EndIf
Next
End Sub
DataOrig:
LOAD *
Inline [
String, Other Fields
123444566556789, Other Values
123444566556789, Other Values 2
];
Data: LOAD * Inline [DumbField];
For vFieldValue=1 to FieldValueCount('String')
LET vString = FieldValue('String', $(vFieldValue));
CALL RemoveDuplicates('$(vString)')
Concatenate (Data) LOAD '$(vText)' as String, [Other Fields] Resident DataOrig Where String='$(vString)';
Next
DROP Table DataOrig;
DROP Field DumbField;
You can build your own macro function to do this:
function getTarget(source)
s=""
t=""
for i = 1 to len(source)
r=mid(source,i,1)
if StrComp(r,t)<>0 then
s=s+r
t=r
end if
next
getTarget=s
end function
In the Script:
LOAD
...
source,
getTarget(source) as target,
...
Hi,
a single expression solution for pure numeral strings could be:
LOAD *, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(PurgeChar(Capitalize(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Str,'0',' a'),'1',' b'),'2',' c'),'3',' d'),'4',' e'),'5',' f'),'6',' g'),'7',' h'),'8',' i'),'9',' j'),'a a','aa'),'b b','bb'),'c c','cc'),'d d','dd'),'e e','ee'),'f f','ff'),'g g','gg'),'h h','hh'),'i i','ii'),'j j','jj'),'a a','aa'),'b b','bb'),'c c','cc'),'d d','dd'),'e e','ee'),'f f','ff'),'g g','gg'),'h h','hh'),'i i','ii'),'j j','jj')),' abcdefghij'),'A','0'),'B','1'),'C','2'),'D','3'),'E','4'),'F','5'),'G','6'),'H','7'),'I','8'),'J','9') as Str2
Inline [
Str
123444566556789
11122344456565432123456789
2345678
23343435687
244445466788
73669843889
];
hope this helps
regards
Marco
Hello,
Thanks a lot for all the replies.
Still figuring out which one is the best for me, but all those I tested seem to be working perfectly!
The only thing I couldn't get to work is the regex proposal, but plenty of other options now
Kind regards,
Maarten