Alphabetical order of string

[#1]

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

[SergeyMak]

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?

[#3]

Hi,

the final result for FIELD2 should be:

A : WXY

B: ABC

C: MNO

[SergeyMak]

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;

[anbu1984]

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;

[ToniKautto]

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?

[maxgro]

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;

[tresesco]

Massimo Grossi, another interesting idea !