10 Replies Latest reply: Dec 18, 2015 9:49 AM by Peter Cammaert RSS

    Custom sort ???

    ahmar ansari

      hello

      i have a field model as you can see below

       

      Model

      <2
      Total <2

      12 i

      25 k

      2-3

      Total 2-3
      29 k
      26 k
      3-4
      29 j
      26 j
      25 j
      Total model
      Total 3-4

       

      i want to sort it as below in pivot table

       

      Model

      <2

      12 i

      Total <2

      2-3

      25 k
      26 k
      29 k
      Total 2-3
      3-4
      25 j
      26 j
      29 j
      Total 3-4
      Total model

       

      Thanks & Regards

       

      Ahmar

        • Re: Custom sort ???
          Tamil Nagaraj

          Ji Ahmar,

           

          Try sort expression as

           

          =Match(FieldName,'B','A','Z','Y')

           

          Capture.PNG

          • Re: Custom sort ???
            balraj ahlawat

            Sort - > Expression ->

             

            Try like this:

             

            =if(Model='<2',1,

              if(Model='12 i',2,

            if(Model='Total <2',3,

                if(Model='2-3',4,

                 if(Model='25 K',5,

                    if(Model='26 K',6,                          ////and so on.......

                       if(Model='29 K',7)))))))  

             

             

            Then sort is ascending or descending

            • Re: Custom sort ???
              jagan mohan rao appala

              Hi,

               

              Try like this, add below script in your script

               

              ModelSort:

              LOAD

              *

              INLINE [

              Model, Order

              <2, 1

              12 i, 2

              Total <2, 3

              2-3, 4

              25 k, 5

              26 k, 6

              29 k, 7

              Total 2-3, 8

              3-4, 9

              25 j, 10

              26 j, 11

              29 j, 12

              Total 3-4, 13

              Total model, 14

              ];

               

               

              Now in Pivot table Sort Tab select give Order in Expression text box.

               

              Hope this helps you.

               

              Regards,

              jagan.

              • Re: Custom sort ???
                ahmar ansari

                hi all

                my model field is actually a combination of model,submodel and total fields.

                I want to sort my data both this field in a single dimension

                • Re: Custom sort ???
                  Andrew Walker

                  Hi Ahmar,

                  At the beginning of you script add this:

                   

                  Temp:

                  LOAD * INLINE [

                      Model

                      <2

                      Total <2

                      12 i

                      25 k

                      2-3

                      Total 2-3

                      29 k

                      26 k

                      3-4

                      29 j

                      26 j

                      25 j

                      Total model

                      Total 3-4

                  ];

                   

                  This sets the load order for the elements of the field Model. At the end of the script

                   

                  DROP Table Temp;

                   

                  This gets rid of the temporary table but QlikView retains the load order of the field values. Now when you want to sort in a chart set the sort order of the field Model to Load Order.

                  • Re: Custom sort ???
                    Sebastian Lettner

                    Hi,

                     

                    try this. You can use it with a range from I think <1 to 8-9 and 1 A to 999 z

                     

                    //SORT ORDER
                    //ZYXXXWWW
                    //Z....... number range
                    //.Y...... head or total flag (0 or 9)
                    //..XXX... int based range sort    (Must be between 0 and 999)
                    //.....WWW char based range sort (Ord(chr) must be between 0 and 999)
                    Sort:
                    LOAD
                        Model,
                        if(IsNum(Left(Model,1)),    //Check for number as start
                            if(IsNum(Mid(Model,2,1)),    //check if second char is a number
                                (Num#(Left(Model,1)) * 10000000) + (Num#(Left(Model, Index(Model,' '))) * 1000) + (Ord(Mid(Model, Index(Model,' ') + 1, 1))),    // Make sort int based on string like [int][int][char]
                                Num#(Left(Model,1)) * 10000000),    //Make sortorder based on first int
                            if(Left(Model, 1) = '<' ,    //check for '<' as start
                                (Num#(Mid(Model, 2, 1)) - 1) * 10000000,    //make sort order for <[int] ([int] - 1)
                                if(IsNum(Mid(Model, Index(Model,' ') + 1, 1)),    //check for number after space (like total [int]-[int])
                                    (Num#(Mid(Model, Index(Model,' ') + 1, 1)) * 10000000) + 9000000, //make sort int based on first int after space
                                     if(Mid(Model, Index(Model,' ') + 1, 1) = '<',    //check for '<' after space (like <[int])
                                         (Num#(Mid(Model, Index(Model,' ') + 2, 1) - 1)* 10000000) + 9000000, //make sort int based on first int after '<'
                                         99999999))    // sort order for last line (like total model)
                                )
                            ) as ModelSort
                    Inline
                    [
                        Model
                        <2
                        12 i
                        Total <2
                        2-3
                        25 k
                        26 k
                        29 k
                        Total 2-3
                        3-4
                        25 j
                        26 j
                        29 j
                        Total 3-4
                        Total model
                    ];
                    
                    
                    
                    

                     

                    Regards

                    Sebastian Lettner