Simple list box sort

[#1]

Hello all,

I can't believe I am struggling with this! I have a set of values that I want to display in a list box:

0000CARRIAGE

001ISLEOFMAN

02NMX20147-2

02NMX20147-4

03-20-009/23

0011A1000065

0065B1006175

0070A1004358

0073A9920262

440KEY05HGHB

700KEY080TNT

0900FU0569FO

000000001391

9820-0004/13

000000016209

099742-10700

I want them to be displayed in the sequence (as per Excel sort ascending):

000000001391

000000016209

0000CARRIAGE

0011A1000065

001ISLEOFMAN

0065B1006175

0070A1004358

0073A9920262

02NMX20147-2

02NMX20147-4

03-20-009/23

0900FU0569FO

099742-10700

440KEY05HGHB

700KEY080TNT

9820-0004/13

I load the data using

Load text(Field1) as TextField...

I would expect a simple sort on State and Text on the listbox properties would do it but...!!!

Thanks!

Gordon

[rwunderlich]

Gordon,

I believe you are seeing the "natural sorting" alogorithm. See this thread for more info:

http://community.qlik.com/message/27733#27733

-Rob

http://robwunderlich.com

[#3]

Other solution if SortField has 12 character lenght

tmpData:
load *,
 mid(tmpSortField,1,1) as c1,
 mid(tmpSortField,2,1) as c2,
 mid(tmpSortField,3,1) as c3,
 mid(tmpSortField,4,1) as c4,
 mid(tmpSortField,5,1) as c5,
 mid(tmpSortField,6,1) as c6,
 mid(tmpSortField,7,1) as c7,
 mid(tmpSortField,8,1) as c8,
 mid(tmpSortField,9,1) as c9,
 mid(tmpSortField,10,1) as c10,
 mid(tmpSortField,11,1) as c11,
 mid(tmpSortField,12,1) as c12
;
LOAD text(SortField) as tmpSortField
FROM
[t1.xls]
(biff, embedded labels, table is t1$)
;
Data:
load tmpSortField as SortField
resident tmpData
order by c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12
;
drop table tmpData;

[#4]

Hi all,

I see now how the 'natural sorting' algorithm is being applied (thanks Rob).

As Nathan suggests, creating a list box based on the expression of the field 'Part No' as text

text([Part No])

works fine.

However, this key field is used in several charts etc and so I had hoped that changing the sort sequence to the expression text([Part No]) and text would also work but it doesnt.Particularily when I force it to text in the load script!

I have attached the full list of 'Part No' (26k+).

Is there an easy way of doing this rather than having to constantly use an expression instead, go through hoops in the load script or is it just a problem with v9sr4?

Thanks!

Gordon

[#5]

With the script i posted it works, you only have to use a listbox with sort based on Load Order = Origional.

Regards

[#6]

A good workaround but a workaround for what should be a simple thing to achieve.

Thanks for your help.

Regards,

gordon

[#7]

I'm agree with you. It should be simpler.

Regards