topic Enforce Custom Sorting in QlikView

Enforce Custom Sorting

Tue, 11 Oct 2011 16:06:49 GMT

Hey,

I have to enforce custom sorting to a table how can I do it. I want the table to be always in that sort order and it is not in alphabetical order. For example, I have fields in a table 'businessA', 'businessB', 'businessC', 'businessD' and 'businessE'. Now I need to sort in order as BusinessB,D,E,A,C . I want these fileds be sorted via loadscript. I did load them via inline load like

Tablename:

Load * inline

[businessType, sortorder

'businessA', 4

businessB', 1

businessC', 5

businessD', 2

businessE', 3];

and used =only(sortorder) while sorting but this doesnot get sorted once I used the table for cyclic group. How can I do this.

Regards,

Abha

Re: Enforce Custom Sorting

Tue, 11 Oct 2011 16:19:08 GMT

You can achive this by using "sort by expression". Attached the example, hope this helps you.

Regards,

Kiran.

Re: Enforce Custom Sorting

Tue, 11 Oct 2011 17:25:47 GMT

Thank you Kiran for the answer, but I have already tried that and that is not what I wanted. What I wanted was when this table is loaded it will automatically sorted in this manner without using any wizards or options via script may be.

Regards,

Abha

Re: Enforce Custom Sorting

Tue, 11 Oct 2011 17:30:09 GMT

Is sort by Load order option ok to you? Then you can use the order by in resident load.

Kiran.

Re: Enforce Custom Sorting

jonathandienst — Tue, 11 Oct 2011 19:05:34 GMT

Hi Abha

If you turn the businessType into a dual, then it will sort automatically (by value). This is your example, converted to produce a dual field:

Tablename:

Load Dual(businessType, sortorder) As businessType

Inline

[

businessType, sortorder

businessA, 4

businessB, 1

businessC, 5

businessD, 2

businessE, 3

];

Now QV will consider businessType to be a numeric type with value sortorder, and text representation 'businessA', 'businessB' etc. See the manual for more information on dual values. I also have some information at my blog, here.

Hope this helps

Jonathan

Re: Enforce Custom Sorting

Wed, 12 Oct 2011 16:54:40 GMT

Thank you Jonathan, your script doesnot sort automatically in the sequence I originally talked about i.e. B-D-E-A-C .

I need the businesstype permanently sorted in this manner so we donot have to change in properties of any sheet objects but it automatically align itself in above mentioned sequence. Can this be acheived. Please guide me, I am fairly new to the tool.

Regards,

Abha

Re: Enforce Custom Sorting

Wed, 12 Oct 2011 17:04:23 GMT

Hi Abha,

If you want the sort order to be fixed at document level, go to Document properties->Sort. Select the field and specify the sort order. That rule will be applied throught the document where this field is used.

Regards,

Kiran.

Re: Enforce Custom Sorting

Wed, 12 Oct 2011 17:26:29 GMT

Thank you so much Kiran.

Now one more query what if these fields are from a database table, How can we custom sort fields from a database table in the B-D-E-A-C sequence ? or can we even do it?

Regards,

Abha

Enforce Custom Sorting

Wed, 12 Oct 2011 17:27:54 GMT

Is the load order defined in the database or is custom?

Regards,

Kiran.

Enforce Custom Sorting

Wed, 12 Oct 2011 19:12:42 GMT

Hi Abha, you can pull the "sortorder" column into your table, Sort it in Ascending order and Hide column sortorder...give it a try and see.

Re: Enforce Custom Sorting

jonathandienst — Thu, 13 Oct 2011 08:06:42 GMT

Abha

The dual values do work - I said that Qv will consider the value to be a numeric, so you need to the sort order to numeric for them to work.

That creates a permanent sort order for the field, so you don't need hidden sort fields or sort expressions.

Regards

Jonathan