Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Order by expression in Multi Box

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.

1 Solution

Accepted Solutions
erichshiino
Partner - Master
Partner - Master

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

;

View solution in original post

5 Replies
erichshiino
Partner - Master
Partner - Master

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

Not applicable
Author

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?

erichshiino
Partner - Master
Partner - Master

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

;

Not applicable
Author

Thanks Erich, that works!

Not applicable
Author

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.