QSOW sort order with letter and number mixed does not work

[simonaubert]

Hello all,

Here a very simple script 

test_order:
LOAD text(article_reference) as article_reference;
LOAD * Inline
[article_reference
z22091AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
z3LETOAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
z22092AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
z22093AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
]
; 	

NoConcatenate
test_order2:
load * resident test_order order by article_reference; drop table test_order;

exit script;

I try now to sort my article_reference field in script for future but even in chart... the result is more than strange :

QSOW May 2022 Patch 6

How can I make it work?

(NB : of course, the target is not a load inline but a qvd... so i can't change the order in the load inline)

Best regards,

Simon

[simonaubert · Accepted solution]

Here the solution I found, that's pretty dirty.

the issue was even in a string, 3 is before 22 (it's not a real ascii sort). So let destroy 22 and let's compare ¤3 to ¤2¤2 by adding a non numeric character before every numeric character

NoConcatenate
TMP_Creation_Date_Article:
LOAD distinct


	Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
  text(related_cwh_item_code)
  ,
  '0','¤0'),'1','¤1'),'2','¤2'),'3','¤3'),'4','¤4'),'5','¤5'),'6','¤6'),'7','¤7'),'8','¤8'),'9','¤9')
  as article_reference, //replace with ¤ is here to control the sort order. witout it 3 is before 22 even in a string
  Date(create_date)				as article_creation_date,
  ddj
resident LOAD_Tx_Article 
;
inner join(TMP_Creation_Date_Article)
LOAD 
max(ddj)as ddj
resident LOAD_Tx_Article ;

DROP TABLES LOAD_Tx_Article ;

// exit script;

NoConcatenate
TMP_Creation_Date_Article_Ordered:
LOAD *,
RowNo() as article_bi_id
;
LOAD
  text(replace(article_reference,'¤','')) as article_reference,
  article_creation_date,
  ddj
  ;
LOAD * 
resident TMP_Creation_Date_Article
Order by
article_creation_date asc,
  article_reference asc  
;

DROP TABLE TMP_Creation_Date_Article;
exit script;