Wrong Sorting of Alphanumeric Data During Load Script

[micheledenardi]

Dear All,

i have to order an alphanumeric field during the load script but seems the standard function Order by works by"sorting is made primarily by numeric value and secondarily by national collation order". And probably this is not what i neeed.

This the example script:

Temp:
NoConcatenate
Load *
Inline [
MATNR
C5AAA
C03403
C6AAA
C03403 A
C03403 B
C03403 1
C03403 2];

Final:
NoConcatenate
Load
	MATNR
Resident Temp
	Order by MATNR ASC;
Drop Table Temp;

And the result is:

But the sort order i need is basically sorting considering each characters to obtain this:

MATNR

C03403

C03403 1

C03403 2

C03403 A

C03403 B

C5AAA

C6AAA

Any trick?

[marcus_sommer]

Maybe a split of the values is helpful and then sorting with them:

Temp:
NoConcatenate
Load *, subfield(MATNR, ' ', 1) as F1, subfield(MATNR, ' ', 2) as F2
Inline [
MATNR
C5AAA
C03403
C6AAA
C03403 A
C03403 B
C03403 1
C03403 2];

Final:
NoConcatenate
Load
MATNR, F1, F2, recno() as RecNo, rowno() as RowNo
Resident Temp
Order by F1 desc, F2;
Drop Table Temp;

- Marcus

[micheledenardi]

Thanks Marcus, this can be an approach.

But, since i cannot monitor how many spaces the business set on this field, i need to find another "more dynamic" way.

[Aurelien_Martinez · Accepted solution]

Hi,

I have tried by replace number by letter

test:
Mapping Load *
inline [
num, let
0, _A
1, _B
2, _C
3, _D
4, _E
5, _F
6, _G
7, _H
8, _I
9, _J
];

Temp:
NoConcatenate
Load MapSubString('test', MATNR) as tt,*
Inline [
MATNR
C5AAA
C03403
C6AAA
C03403 A
C03403 B
C03403 1
C03403 2];

Final:
NoConcatenate
Load
MATNR,
tt
Resident Temp
Order by tt, MATNR asc;
Drop Table Temp;

Best,

Aurélien

[marcus_sommer]

Multiple spaces might be removed with a replace() or a trim() or also a mapsubstring(). Especially the last could be useful if not only be an unknown number of spaces are used as delimiter else multiple other chars like -_/"# and whatever else the business may do. Afterwards maybe a substringcount() to check how many sub-strings are there to consider them, too within the sorting. It could become an ugly job ...

- Marcus

[micheledenardi]

Nice Solution @Aurelien_Martinez 

Thanks a lot also to your @marcus_sommer