Hi Community.
I need your help. I would like to sort the characters in a string alphabetically in a new field in the load script.
For example:
Myfield Newfield
'abc' 'abc'
'acb' 'abc'
'bca' 'abc'
'zxj' 'jxz'
'yzx' 'xyz'
The Myfield values are unknown so I can't specify various text values to replace in the function.
Any suggestions would be much appreciated.
Here's one way:
MyTable:
LOAD
MyField, Concat(Letter,'',Letter) as NewField
GROUP BY
MyField
;
LOAD
MyField, Mid(MyField,IterNo(),1) as Letter
FROM
...source_table_here...
WHILE
IterNo() <= Len(MyField)
;
Here's one way:
MyTable:
LOAD
MyField, Concat(Letter,'',Letter) as NewField
GROUP BY
MyField
;
LOAD
MyField, Mid(MyField,IterNo(),1) as Letter
FROM
...source_table_here...
WHILE
IterNo() <= Len(MyField)
;
You are awesome Gysbert, thank you!
RetRoute:
LOAD * INLINE [
Rec, Cus, Br, Rev, Col, Del, MyField
1, 1, 77, 100, HBN, Auck, HBN-Auck
2, 1, 33, 2300, Nel, Has, Nel-Has
3, 1, 33, 290, Has, Auck, Has-Auck
4, 2, 22, 200, Has, Nel, Has-Nel
5, 3, 77, 150, Auck, Has, Auck-Has
6, 3, 88, 50, HBN, Nel, HBN-Nel
7, 3, 77, 300, Nel, HBN, Nel-HBN
8, 3, 77, 800, HBN, Auck, HBN-Auck
9, 5, 44, 350, Auck, Has, Auck-Has
10, 5, 55, 900, Has, Nel, Has-Nel
];
MyTable:
LOAD Rec,
MyField, Concat(Letter,'',Letter) as NewField
GROUP BY
Rec,MyField
;
LOAD Rec,
MyField, Mid(MyField,IterNo(),1) as Letter
Resident RetRoute
WHILE
IterNo() <= Len(MyField)
;
Drop table RetRoute
;
Edit: Script added