Straight Table - Sort Dimension by more than one criterium

Fri, 20 Jan 2012 09:09:55 GMT

I'm new to QlikView and new to the QlikCommunity.

My problem is that I do not know how to sort a Straight Table on two criteria.

It's a table of S&P credit ratings:

A-

A+

AA-

AAA

B+
BB

BB-

...etc

I want to sort the ratings, firstly, in ascending alphabetical order (ie. A -> Z) based on the first character then, secondly, on the second character, thirdly, on the third character, and so on so that they look more like this:

AAA

AA+

AA-

A+

A-

BBB+

BBB

BBB-

BB+

BB-

B+

B-

...etc

Can this be done with an Expression?

Kind regards

Straight Table - Sort Dimension by more than one criterium

swuehl — Fri, 20 Jan 2012 11:16:48 GMT

Maybe you can come up with some complex expression to sort it like you need.

But I believe there are more simple ways to achieve this, since your credit ratings should be a defined set of values:

Either load your set of credit ratings with a inline load prior loading your tables with the ratings, thus the load order of the table could be used as sort order:

CreditRatings:

LOAD * INLINE [

Rating

AAA

AA+

AA-

A+

A-

BBB+

BBB

BBB-

BB+

BB-

B+

B-

];

(add more lines as needed. In your chart, use load order as sort order)

Or explicitely set a numerical to each string, using this numerical as sort order:

CreditRating:

LOAD dual(Rating,Sortorder) as Rating INLINE [

Rating, Sortorder

AAA,1

AA+,2

AA,3

AA-,4

A+,5

A,6

A-,7

BBB+,8

BBB,9

BBB-,10

BB+,11

BB,12

BB-,13

B+,14

B,15

B-,16

];

Hope this helps,

Stefan

topic Straight Table - Sort Dimension by more than one criterium in QlikView

Straight Table - Sort Dimension by more than one criterium

Straight Table - Sort Dimension by more than one criterium