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.

[florentina_doga]

use script

aa:

load * inline [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

];

NoConcatenate

bb:

load *

resident aa order by order;

drop table aa;

now table is order.

[#3]

Yes, It could be a solution but I do not want to touch it using script. I would like to do it using an expression in list box order tab. Is it possible in some way? I do not understand but dual function is not working at all. Item "Show More Values" is not ordered as the rest, it always remains at first position of the list box.

[jonathandienst]

You did change the sort to numeric, right?

[#5]

What do you mean? In the list box properties, in order tab, I have only selected "Expression" in the order by section and then I set the expression to:

=dual([name_extended], order)

[#6]

Hi,

I tried out your expression in the sort and it is working fine. Maybe I'm not understanding.

Can you please take a look at the attached qvw and check?

Thanks!

- Stan

[Digvijay_Singh]

Dual only works when number formatting is not disturbed and kept as expression default. You may verify that.

[jonathandienst]

Tony TP wrote:

What do you mean? In the list box properties, in order tab, I have only selected "Expression" in the order by section and then I set the expression to:

=dual([name_extended], order)

Ok, I thought you put the dual expression in the list box - then you could sort it numerically, as a dual is actually a number, with the text representation given by dual expression.

But if you want to put something on the sort "Expression" box, you can just use your order field since the order and the name_extended fields are associated.

[el_aprendiz111]

Hello,

everything is fine just missing in the properties:

[#10]

Yes, the example you provide works but I do not know why in my concrete case, getting data from an excel into a table using loading, seems it does not work. Once data loaded I can preview data in table an is correct. Anyway sorting is not working in my list box by doing the same as in this example. I do not know why.