topic Re: Simple list box sort in QlikView

Simple list box sort

Tue, 24 May 2011 11:24:12 GMT

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

Re: Simple list box sort

perumal_41 — Tue, 24 May 2011 11:54:17 GMT

Please Check Is correct or not

Re: Simple list box sort

ToniKautto — Tue, 24 May 2011 12:03:45 GMT

Setting sort order to Text = A>Z should to the trick. Do you have any other sorting enabled at the same time?

If you still have this problem, please attach a sample QVW, so that your settings can be evaluated.

Re: Simple list box sort

Tue, 24 May 2011 13:25:10 GMT

Guys,

The list box properties are nothing more than state & Text A>Z.

This is very weird.

I have attached a xls with the data and a simple qvw.

I am using V9SR4 - how about you?

Regards,

Gordon

Re: Simple list box sort

ToniKautto — Tue, 24 May 2011 13:42:03 GMT

This looks like a bug to me, and I can also reproduce it on QV10 SR2.

Please report it to QlikView support for further evaluation.

support@qlik.com or http://qlik.com/Support

Simple list box sort

Tue, 24 May 2011 13:51:46 GMT

I have now reported it to support and will post the response.

Regards,

Gordon

Re: Simple list box sort

montubhardwaj — Tue, 24 May 2011 13:59:48 GMT

Ok. You can have the values sorted in excel( as you said, you want them to be sorted as per excel) and then load into QV. Then you can sort it based on Load Order=> Origional.

Simple list box sort

Tue, 24 May 2011 14:10:33 GMT

The data is actually just an example subset of a .csv file that is regularily FTPd and contains 26k rows, so that is not an option unfortunately.

Regards,

Gordon

Re: Simple list box sort

nathanfurby — Tue, 24 May 2011 15:03:07 GMT

Hey Gordon - I've seen this happen with List Boxes before. Doesn't seem to be any reason why it doesn't sort.

I think the attached example could be a work around - using a Text() expression rather than the field itself.

Re: Simple list box sort

Tue, 24 May 2011 15:09:54 GMT

Thanks Nathan,

Very odd behaviour isnt it - a good workaround in the meantime though.

Regards,

Gordon

Simple list box sort

rwunderlich — Tue, 24 May 2011 15:24:31 GMT

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

Re: Simple list box sort

Tue, 24 May 2011 15:35:20 GMT

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;

Re: Simple list box sort

Fri, 27 May 2011 11:52:25 GMT

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

Re: Simple list box sort

Sat, 28 May 2011 08:04:42 GMT

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

Regards

Re: Simple list box sort

Sat, 28 May 2011 08:46:53 GMT

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

Thanks for your help.

Regards,

gordon

Re: Simple list box sort

Sat, 28 May 2011 16:26:14 GMT

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

Regards