topic Re: Order list box items using an expression in QlikView

Order list box items using an expression

Thu, 15 Sep 2016 14:40:11 GMT

I have a list box with some values, let's say:

Car

Bus

Metro

Airplane

Train

Helicopter

List box was populated from a table field that was loaded from an excel.

I would like to order them in order to be displayed as below:

Train

Metro

Car

Bus

Helicopter

Airplane

but I want to order them using an expression in the list box order tab. How can I do this? I do not want to order them in the script.

Also, If I want to put an element always at the end, for example, put Metro item always at the end of the list, how can I do this as well?

-------------------------------------------------------------------------------

I have not explained so well so I am explaining it in details. I would like to use dual expression. Suppose I have more items than above indicated.

In my script I load the information from excel as below:

LOAD

name,

description,

cost,

if(trim(name)='Train' or trim(name)='Metro' or trim(name)='Car' or trim(name)='Bus' or trim(name)='Helicopter', name, "Show More Values\' & name) as name_extended,

if(trim(name)='Train', 1, if(trim(name)='Metro', 2, if(trim(name)='Car', 3, if(trim(name)='Bus', 4, if(trim(name)='Helicopter', 5, 6))))) as order

FROM

[myexcel.xlsx]

I assign a corrleative number for the more used ones 1 to 5 and I assign 6 to the rest.

So with this I show the most used first and the rest collapsed. I enable tree view in the list box and caracter separator '\'.

Then in the order tab list box I use below expression:

=dual([name_extended], order) and I select ascendent, but it is not working. I want item order in this sequence: 1, 2, 3, 4, 5 and then the rest of items marked with 6.

Re: Order list box items using an expression

krishnacbe — Thu, 15 Sep 2016 14:43:01 GMT

Hi,

You can use belwo expression in Sort tab.

Match(Field_Name,'Train','Metro','Car','Bus','Helicopter','Airplane')

Hope this helps.

Re: Order list box items using an expression

florentina_doga — Thu, 15 Sep 2016 14:49:55 GMT

see attach

Re: Order list box items using an expression

florentina_doga — Thu, 15 Sep 2016 14:50:35 GMT

use dual in sort

Re: Order list box items using an expression

Thu, 15 Sep 2016 15:33:13 GMT

I like your solution so I have tried to do it. I have edit my post as I have not explained so well. I have explained in details. But your solution is not working for me. Please, see my edit below '-----------'.

Re: Order list box items using an expression

Fri, 16 Sep 2016 08:00:36 GMT

Your solution does not work in my case and I do not know why. Your example work great! The only difference between your example and my case is that you use an inline table and I create the table in my script loading it from an excel:

LOAD

name,

description,

cost,

if(trim(name)='Train' or trim(name)='Metro' or trim(name)='Car' or trim(name)='Bus' ortrim(name)='Helicopter', name, "Show More Values\' & name) as name_extended,

if(trim(name)='Train', 1, if(trim(name)='Metro', 2, if(trim(name)='Car', 3, if(trim(name)='Bus', 4,if(trim(name)='Helicopter', 5, 6))))) as order

FROM

[myexcel.xlsx]

Please, see my post edited after line "------------------". Why in my case is not working? In my case "Show more values" item is always put in the first position despite ordering desc or asc. What's the problem?