1 Reply Latest reply: Jan 20, 2012 6:16 AM by Stefan Wühl RSS

    Straight Table - Sort Dimension by more than one criterium

      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-

      A+

      AA

      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

      AA-

      A+

      A

      A-

      BBB+

      BBB

      BBB-

      BB+

      BB

      BB-

      B+

      B

      B-

      ...etc

       

      Can this be done with an Expression?

       

      Kind regards

        • Straight Table - Sort Dimension by more than one criterium
          Stefan Wühl

          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

          AA-

          A+

          A

          A-

          BBB+

          BBB

          BBB-

          BB+

          BB

          BB-

          B+

          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