topic Re: Custom sort in staright table in QlikView

Custom sort in staright table

PreranaLH — Tue, 20 Apr 2021 03:08:04 GMT

Hi i have table below with Risk as dimension and other as Expression.

I want sorting by Inherent/Residual col - by custom sort (it should be H-M-L)

i am trying to put match(Inherent,'H','M','L')in Sorting tab but dont know why the option is disabled .

even i tried removing # col - which is showing Rowno()

Re: Custom sort in staright table

Dalton_Ruer — Thu, 22 Apr 2021 14:36:14 GMT

You have a couple of other options that don't involve any expressions for sorting so that things are faster.

1. Force the sort order you want using an Inline table that builds the field in the order that you wish like this. Notice that I pre-build just the field(s) I want in its own table, load them in the manner I wish and then drop the forced table since the field is already now stored:

InherentSort:
Load * Inline [
Inherent
L
M
H
];

Data:
Load * Inline[
Field1, Field2, Inherent, Residual
A, 19, H, M
B, 29, L, H
C, 30, M, M
D, 31, H, L
];

Drop Table InherentSort;

Then all you have to do is declare for the field you want to use the "Load Order"

2. The second option takes advantage of the DUAL datatype. It allows you to maintain a STRING version of your field as well as a NUMERIC version of your field. For flags you can have "Yes"/1 and "No"/0. For things like custom sort orders the number works great for sorting. The reason I like this is besides your custom sort order for high, medium and low it can be used for things like January and April when sorting months. Notice that I simply used your expression during the load script to generate the number I want stored for the numeric part.

Data:
Load Field1, Field2, Dual(Inherent, Match(Inherent,'H','M','L')) as Inherent, Dual(Residual, Match(Residual,'H','M','L')) as Residual;
Load * Inline[
Field1, Field2, Inherent, Residual
A, 19, H, M
B, 29, L, H
C, 30, M, M
D, 31, H, L
];

Then you just need to say sort numeric

Re: Custom sort in staright table

Ryan4 — Fri, 23 Apr 2021 14:13:14 GMT

I'm already using an IF expression to color the bars per company (NCIA)

Re: Custom sort in staright table

Kasey001 — Mon, 25 Oct 2021 17:43:57 GMT

I'm already using an IF expression to color the bars per company (NCIA https://mythdhr.vip/). The reason I like this is besides your custom sort order for high https://mycoles.vip/, medium and low it can be used for things like January.