topic Order by expression in Multi Box in QlikView

Order by expression in Multi Box

Wed, 08 Jun 2011 11:25:17 GMT

Ok, so I have a little application with just two fields:

Name

NameSortOrder

I am populating with just a few values in an inline load

TestOrdering:

load * inline [

Name,NameSortOrder

'--- Global', '--- Global'

'-- Commercial', '-- Commercial'

'-- Insurance', '-- Insurance'

'- Commercial Subgroup', '-- Commercial - Commercial Subgroup' ];

When I create a multi box, showing Name, but ordering by the expression NameSortOrder, this doesn't seem to work. Iam hoping to see '- Commercial Subgroup' between '-- Commercial' and '-- Insurance' , instead it appears at the end of the list.

Order by expression in Multi Box

erichshiino — Wed, 08 Jun 2011 11:33:19 GMT

I guess that if you want to order it by expression, QV will try to order the numbers.

Then, it would be easier if you could create another column, like this:

TestOrdering:

load * inline [

Name,NameSortOrder,Order

'--- Global', '--- Global' ,1

'-- Commercial', '-- Commercial', 2

'-- Insurance', '-- Insurance',4

'- Commercial Subgroup', '-- Commercial - Commercial Subgroup',3 ];

Then, you sort it by Order

Hope this helps,

Erich

Order by expression in Multi Box

Wed, 08 Jun 2011 11:39:55 GMT

Thanks Erich, that seems to work better. However it presents another issue. In my little test app with an inline load, this is fine. However in my real app I won't know what items to expect and could do with generating a sort order based on the text field. Any ideas how to achieve this?

Order by expression in Multi Box

erichshiino — Wed, 08 Jun 2011 11:45:04 GMT

You could create a order field dynamically on your script:

TestOrdering:

load * inline [

Name,NameSortOrder

'--- Global', '--- Global'

'-- Commercial', '-- Commercial'

'-- Insurance', '-- Insurance'

'- Commercial Subgroup', '-- Commercial - Commercial Subgroup'];

Order:

load NameSortOrder, rowno() as Order

Resident TestOrdering

order by NameSortOrder asc

;

Order by expression in Multi Box

Wed, 08 Jun 2011 11:52:18 GMT

Thanks Erich, that works!

Order by expression in Multi Box

Mon, 20 Jun 2011 13:19:54 GMT

Unfortunately this didn't quite work in my QVW, or at least it won't work until I rework the data.

Consider this inline load

Order:

load * inline [

Name, SortOrder, %Key

'--- Global', 1, 1

'-- Commercial', 2, 1

'-- Insurance', 4, 1

'- Commercial Subgroup',3, 1

'--- Global', 1, 2

'-- Commercial', 2, 2

'-- Insurance', 4, 2

'- Commercial Subgroup', 3, 2 ];

I have now added a field %Key, which I'm going to use to link to another table. Now, having build a Name multi box, ordered by expression 'SortOrder', when no Name is selected the list is ordered correctly. But when I select a Name, the list is no longer sorted.

I have reported this and it has been acknowledged as a bug.