topic Re: Custom sorting in QlikView

Custom sorting

d_koti24 — Sat, 25 Oct 2014 12:46:31 GMT

i have a month field.how to sort the month field like -----jan,feb,mar,....dec .

Re: Custom sorting

ecolomer — Sat, 25 Oct 2014 12:54:19 GMT

This ordenation is natural by default.

You can also select order by MonthNo(month) in Ordenation Label,

Re: Custom sorting

its_anandrjs — Sat, 25 Oct 2014 12:56:50 GMT

It will sort by default ascending order or also you can use Sort option from the chart properties.

Re: Custom sorting

rwunderlich — Sat, 25 Oct 2014 13:09:20 GMT

They will sort properly if they were created as dual() values. You would get a dual value if you used the month() function to extract from a date.

If the months come to you as a text value, you can create a proper dual like this:

month(Date#(monthField','MMM'))

-Rob

Re: Custom sorting

d_koti24 — Sat, 25 Oct 2014 13:40:51 GMT

i wrote like this,its working fine.but i ve a small doubt,is there any performance issue by writing like this,

if(month='jan',1,if(month='feb',2,if(month='mar',3,if(month='apr',4,if(month='may',5,if(month='jun',6,if(month='july',7,if(month='aug',8,if(month='sep',9,if(month='oct',10,if(month='nov',11,12)))))))))))

Re: Custom sorting

its_anandrjs — Sat, 25 Oct 2014 13:58:58 GMT

Yes might be you can use any sort order field also for this but you need to load a simple inline table in the load script and connect it with the month field see the example please.

Fact:

LOAD * Inline

[

Month

apr

may

sep

oct

jun

jan

feb

mar

jul

aug

nov

dec

];

OrderTable:

LOAD * Inline

[

Month,SortOrder

jan,1

feb,2

mar,3

apr,4

may,5

jun,6

jul,7

aug,8

sep,9

oct,10

nov,11

dec,12

];

And you get

This can be done in any chart but with the help of the Expression sort order by using the SortOrder field in the expression box

Regards

Anand

Re: Custom sorting

SunilChauhan — Sat, 25 Oct 2014 14:24:21 GMT

yes if are memory intensive .they degrade the performance of application.

you could use

Match or mixmatch(Month,'jan','feb','mar','apr',.....................................)

or use inline query like anand in script(Control +E).

even sort expressions are degrading the performance.

you could try to sort in script itself.

hope this helps

Re: Custom sorting

rwunderlich — Sun, 26 Oct 2014 10:08:34 GMT

Your example if() statement is probably the worst performing of the various suggestions here. But fine if it works for you.

Another approach to sorting is to load the values in the order you want and then use "load order" for sort. Here's an explanation and example.

http://qlikviewcookbook.com/2012/11/establishing-a-sort-order/

-Rob

http://masterssummit.com

http://robwunderlich.com