9 Replies Latest reply: Sep 19, 2011 3:24 PM by Anand Chouhan RSS

    Sort by Load Order - help needed

    Sudeep Mahapatra

      I've a table named as 'XYZ' and a column named as 'COLM' in it. I've to create a chart and use the 'COLM' as a dimension. The row values in the column 'COLM' are CX,DY,ED,BS,AE,FW but on the chart i've to sort them as AE,CX,BS,DY,ED,FW (custom sort). Can anyone please help me how can i do it?(Heard about Sort by LOAD Order but not sure how to implement it)

      Thanks in advance.

        • Sort by Load Order - help needed
          Anand Chouhan

          Hi,

           

          You have to use wildmatch fuction to sort in data in perticular order.

           

          HTH

          Rgds

          Anand

          • Sort by Load Order - help needed
            Anand Chouhan

            Hi,

             

            In the sort properties of the chart write code to custom sort data so select

            Expression -> Ascending -> and write code -> Match( COLM, 'AE','CX','BS','DY','ED','FW')

            which is your sort order.

             

            And your table is like

            xyz:

            LOAD * INLINE [

              COLM, Value

              CX,251

              DY,154

              ED,241

              BS,286

              AE,123

              FW, 248

            ];

             

             

            HTH

             

            Rgds

            Anand

            • Re: Sort by Load Order - help needed
              Anand Chouhan

              Hi,

               

              See the attached sample file

               

              HTH

              And let me know

               

              Rgds

              Anand

                • Sort by Load Order - help needed
                  Sudeep Mahapatra

                  Hi Anand Thanks a lot for the solution. I tried the Match() and it worked.:) But i had to write the function in each and every Chart. (Sort by Expression condition). I m thinking how to do 'Load order' so that i'll load once and just by selecting "Sort > LOAD Order > original" i'll be able to load the order of the column mentioned in the script.

                  Here is my Sample Code can you pls advice how it can work?

                  XYZSnap.JPG

                  // Loading Table XYZ

                  XYZ:

                  LOAD COLM,

                       COL1,

                       COL2,

                       COL3

                  FROM

                  [..\Data Sources\Sample.xls]

                  (biff, embedded labels, table is XYZ$);

                   

                  XYZInline:

                  LOAD * INLINE [

                       COLM

                       AE

                       CX

                       BS

                       DY

                       ED

                       FW ];

                  drop table XYZInline;

                    • Sort by Load Order - help needed
                      Jason Michaelides

                      You're almost there.  You just need to load the inline table before the main data load so the order is already defined, then drop the inline table after the main data load.  Then, in the chart just tick Load Order (or it might be labelled Original Order - can't remember!) in the sort tab.

                       

                      Hope this helps,

                       

                      Jason

                        • Sort by Load Order - help needed
                          Sudeep Mahapatra

                          Hi Jason thanks a lot. Thats what i was looking for. It is very exciting when it worked as per load order in the script.

                            • Re: Sort by Load Order - help needed
                              Anand Chouhan

                              Hi Sudeep,

                               

                              The jason answer is right but i think if you need different sort orders then on that condition if you load single inline and delete it not works and if you require another sort order so on that use my code like below.

                               

                              xyz:

                              LOAD * INLINE [

                                  COLM, Value, Value2, Value3

                                  CX, 19, 19, 22

                                  DY, 22, 22, 43

                                  ED, 32, 22, 11

                                  BS, 11, 12, 54

                                  AE, 11, 11, 32

                                  FW, 32, 32, 11

                              ];

                               

                              load *,

                              match( COLM, 'AE','CX','BS','DY','ED','FW' ) as SortOrder,

                              match( COLM, 'CX','DY','FW','ED','BS','AE', ) as SortOrder2

                              Resident xyz;

                               

                              Drop table xyz;

                               

                               

                              Rgds,

                              Anand

                          • Sort by Load Order - help needed
                            Pauljin Kochupyloth

                            If you are loading it from a spreadsheet with very limited rows. Make sure the order of values are in the order that you want. Once you use it you can go to sort order and get it order by Load order.

                             

                            Another way is to create an inline table like the one I specified below

                            LOAD * INLINE [

                                 COLM ,Sortorder

                                 AE,1

                                 CX,2

                                 BS,3

                                 DY,3

                                 ED,4

                                 FW,5 ];

                             

                            This time chose the option Sort by an expression and write expression such as sum(Sortorder).

                            It should also give you the custom sort

                            • Re: Sort by Load Order - help needed
                              Anand Chouhan

                              Hi sudeep,

                               

                              Thanks, I explain you are right you need to just load a sort order flied in resident table to make sort order like below code and use that sort order field to sort chart.

                               

                              xyz:

                              LOAD * INLINE [

                                  COLM, Value, Value2, Value3

                                  CX, 19, 19, 22

                                  DY, 22, 22, 43

                                  ED, 32, 22, 11

                                  BS, 11, 12, 54

                                  AE, 11, 11, 32

                                  FW, 32, 32, 11

                              ];

                               

                               

                              load *,

                              Match( COLM, 'AE','CX','BS','DY','ED','FW' ) as SortOrder      //It is a load sequence for xyz table..

                              Resident xyz;

                               

                              Drop table xyz;

                               

                               

                              it generates 1,2,3,4,5,6,7 etc sort orders

                               

                              You need to use this SortOrder field to sort the chart just no need to right code again and again only use field in sort by like ways you able to make so many sort orders.

                               

                              See the updated sample file.

                               

                              And let me know, Thanks once again i am happy to help you.

                               

                              Rgds

                              Anand