topic Re: Problem ordering list box items using an expression in QlikView

Problem ordering list box items using an expression

Fri, 16 Sep 2016 08:40:01 GMT

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.

Re: Problem ordering list box items using an expression

florentina_doga — Fri, 16 Sep 2016 08:51:37 GMT

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.

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 09:50:36 GMT

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.

Re: Problem ordering list box items using an expression

jonathandienst — Fri, 16 Sep 2016 09:57:15 GMT

You did change the sort to numeric, right?

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 10:15:22 GMT

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)

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 12:26:19 GMT

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

Re: Problem ordering list box items using an expression

Digvijay_Singh — Fri, 16 Sep 2016 12:52:34 GMT

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

Re: Problem ordering list box items using an expression

jonathandienst — Fri, 16 Sep 2016 13:00:00 GMT

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.

Re: Problem ordering list box items using an expression

el_aprendiz111 — Fri, 16 Sep 2016 13:03:41 GMT

Hello,

everything is fine just missing in the properties:

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 13:07:17 GMT

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.

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 13:08:57 GMT

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?

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 13:10:09 GMT

Do you mean to put the below expression?

=order instead of dual(...)

It is not working.

Re: Problem ordering list box items using an expression

el_aprendiz111 — Fri, 16 Sep 2016 13:17:09 GMT

This

=dual(, order)

with numerical order

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

Re: Problem ordering list box items using an expression

jonathandienst — Fri, 16 Sep 2016 13:17:16 GMT

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]

Re: Problem ordering list box items using an expression

Fri, 16 Sep 2016 14:23:37 GMT

I just tried Excel now and it is working fine.

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