topic Sorting Macro in QlikView

Sorting Macro

Mon, 23 Apr 2012 09:53:38 GMT

I have to sort a table by dimension where all the selected values should come on top. What will be the macro for that ??

Sorting Macro

d_pranskus — Mon, 23 Apr 2012 11:04:49 GMT

What is the type of sheet object you want this to achieve/

Darius

Sorting Macro

Mon, 23 Apr 2012 11:32:42 GMT

the type of sheet object is straight table

Sorting Macro

d_pranskus — Mon, 23 Apr 2012 11:48:12 GMT

It is better to use list box for that. In QV11 it allows expressions. In this case you will have a posibility to sort selected values at the top and excluded at the bottom.

Cheers

Darius

Sorting Macro

Mon, 23 Apr 2012 11:52:47 GMT

I can not do that. My requirement is such that i have to display the volumes of different products in one table and if i select multiple products. the selected products should be highlighted and the remaining products should also be listed there. But all the selected products should be at the top of the table.

Re: Sorting Macro

d_pranskus — Mon, 23 Apr 2012 12:34:12 GMT

Try this example as starting point

Cheers

Darius

Re: Sorting Macro

jonathandienst — Mon, 23 Apr 2012 13:17:17 GMT

That's not the way Qlikview normally works. Normally, list boxes will display all values, and (depending on the sort criteria) put the selected items on top. Straight tables will only display selected items, non-selected items, or items excluded by selections on other fields will not display at all.

There are some tricks to override the bahaviour in list boxes, but you cannot change the selection behaviour in straight tables.

What you can do is create an island field (eg ProductIsland in DataIsland table) for the products you want to highight. Display this in a list box so you can make select products to highlight.

Define a variable vTest:

=chr(39) & GetFieldSelections(ProductIsland, chr(39) & ',' & chr(39), 10) & chr(39)

(where 10 is the maximum number of selections to allow)

Click the + sign next to the expression to highlight, and select foreground or background colour:

Enter an expression something like:

eg for background:

If(Dimensionality()>0 And Match(Product, $(vTest)) <> 0, Yellow())

And in the sort, enter an expression like (to sort by product), or enter a hidden column and sort by that column (before hiding it). Also disable interactive sorting:

If(Match(Product, $(vTest)), 'z', 'a') & Product

See the attached.

Regards

Jonathan

Edit: Darius' example is better than mine, I think

Re: Sorting Macro

CELAMBARASAN — Mon, 23 Apr 2012 13:28:08 GMT

Hi,

You can do it without macro.

Use Sort by Expression in the sort Tab of the chart properties.

=if(Match(FieldName,$(=chr(39)&GetFieldSelections(FieldName,chr(39)&','&chr(39),20)&chr(39))),0,1)

Instead of 20 you can use maximum number of field values.

Celambarasan