Problem ordering list box items using an expression

[#1]

I want to display some items in a list box but ordered in a certain way. The items I use more, I want to show first, and the rest, collapsed into a treeview that can be expanded when needed. I want the rest to be put at the end of the list (in the below example, they are preceded by "Show More Values\" string). So from my script when I load the data I do the following:

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]

So for example the table after loading have below aspect:

Name           Description            Cost     name_extended                        order

Ship              DescriptionA         100       Show More Values\Ship             6

Car                DescriptionA          20        Car                                             3

Metro            DescriptionA            2        Metro                                          2

Helicopter     DescriptionA         300        Helicopter                                  5

Bus               DescriptionA          10         Bus                                            4

Train             DescriptionA            3        Train                                           1

Truck             DescriptionA          40        Show More Values\Truck           6

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 ( 1 to 5) and the rest at the end under "Show More Values".  "Show More Values" should be the last item. Then I enable tree view in the list box and character separator '\'. So "Show More Values" will be the last item in the list box and it will be contain the rest of items (not ordered)

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

=dual([name_extended], order) and I select ascendent, but it is not working ('Show More Values' Item is always shown at first position in the list box). I want item to be displayed following the order assigned when loading data: 1, 2, 3, 4, 5 and then the rest of items marked with 6.

Why ""Show More Values" is always displayed first and ignore asc/desc option set in the list box order tab?

If I order list box in ascendent mode I want items to be displayed as:

Train

Metro

Car

Bus

Helicopter

Show More Values

When expanded item "Show More Values", the rest of items will be displayed, but "Show More Values\Ship" must be positioned always at the end of the list box.

[#2]

What do you mean by not disturbed and kept as expression? I do not understand what you are trying to tell me. Could you indicate me how can I verify that?

[#3]

Do you mean to put the below expression?

=order instead of dual(...)

It is not working.

[el_aprendiz111]

This

=dual(, order)

with numerical order

2016-09-16 8:11 GMT-05:00 Tony TP <qcwebmaster@qlikview.com>:

[jonathandienst]

I'm not that surprised. I either assign values like this to duals in script and then use numeric sorting, or preload the items in the correct order from the source, and use load order.

The former could be implemented for your load like this:

LOAD name,

  description,

  cost,

  dual(name_extended, order) as name_extended

;

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]

[#6]

I just tried Excel now and it is working fine.

Would you be able to post the qvw and the Excel?