Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a question regarding transforming a string.
Lets say that my data consist of the ff:
FIELD1 | FIELD2 |
A | YXW |
B | CBA |
C | ONM |
I have to sort FIELD2 alphabetically. What function can I use?
Thanks,
Lyn
You should use Order by.
Data1:
LOAD * INLINE [
FIELD1, | FIELD2 |
A, | YXW |
B, | CBA |
C, | ONM |
];
Data:
LOAD *
Resident Data1
Order by FIELD2;
Drop table Data1;
Or you need to sort characters in the every value?
Hi,
the final result for FIELD2 should be:
A : WXY
B: ABC
C: MNO
Hi
PFA
Data1:
LOAD * INLINE [
FIELD1, FIELD2
A, YXW
B, CBA
C, ONMA
];
[tmp]:
LOAD Max(Len(FIELD2)) AS Ln
Resident Data1;
LET vLn=PEEK('Ln');
DROP Table tmp;
NoConcatenate
Data2:
LOAD *,
LEFT(FIELD2,1) AS Field3
Resident Data1;
FOR i=2 to $(vLn)
Concatenate (Data2)
LOAD *,
RIGHT(LEFT(FIELD2,$(i)),1) AS Field3
Resident Data1;
NEXT
DROP Table Data1;
NoConcatenate
Data3:
LOAD DISTINCT *
Resident Data2;
DROP Table Data2;
NoConcatenate
Data4:
LOAD DISTINCT *
Resident Data3
Order BY FIELD2, Field3;
DROP Table Data3;
Data:
LOAD
FIELD1,
FIELD2,
CONCAT(Field3) AS FIELD4
Resident Data4
GROUP BY FIELD1,FIELD2;
DROP TABLE Data4;
Data1:
LOAD * INLINE [
FIELD1, FIELD2
A,YXW
B,CBA
C,ONM
];
NoConcatenate
Data2:
Load FIELD1, Mid(FIELD2,IterNo(),1) As FIELD3 Resident Data1 While IterNo() <= Len(FIELD2);
NoConcatenate
Data3:
Load FIELD1,Concat(FIELD3) Resident Data2 Group by FIELD1;
Drop table Data1;
Drop table Data2;
The reload performance could be poor for string operations like this, so I would recommend evaluating if the text order could be affected at an earlier stage in the data processing.
Another thing to look at is the reason for re-arranging the string. Could there be an other more efficient way to accomplish the business requirement?
maybe
SCRIPT
map:
Mapping
load from, '@' & from as to ;
load * inline [
from
A
B
C
D
E
M
N
O
X
Y
W
];
source:
load * inline [
FIELD1, FIELD2
A, YXW
B, CBA
C, ONM
];
t:
LOAD
FIELD1,
SubField(MapSubString('map', FIELD2), '@') as F
Resident
source;
DROP Table source;
final:
noconcatenate
load
FIELD1,
Concat(F, '', F) as F
Resident t
Group by FIELD1;
DROP Table t;
Massimo Grossi, another interesting idea !