7 Replies Latest reply: Dec 13, 2011 2:12 AM by Sokkorn Cheav RSS

    Sort Inline Table

    alvinford ford

      Hi Friends,

       

      I am unable to sort the below inline statement . Request your help to do that. Want to sort it  based on the MonthNo but could not do that.

       

      Month1:

      load * inline [

      Month,MonthNo

      Jan,1
      Feb,2
      May,5
      Jun,6
      Jul,7
      Aug,8
      Mar,3
      Apr,4
      Sep,9
      Oct,10
      Nov,11
      Dec,12
      ];

       

       

        • Sort Inline Table
          alvinford ford

          Hey I want to do it at the Script Level using the Order By Command.

            • Sort Inline Table
              Sridhar Ethiraj

              Hi,

               

              Check below code helps you or not.

               

              Month1:

              Load Dual(Month,MonthNo) as Month,MonthNo;

              load * inline [

              Month,MonthNo

              Jan,1

              Feb,2

              May,5

              Jun,6

              Jul,7

              Aug,8

              Mar,3

              Apr,4

              Sep,9

              Oct,10

              Nov,11

              Dec,12

              ];

               

              -Sridhar

                • Sort Inline Table
                  alvinford ford

                  Hi Sridhar,

                   

                  Thanks for your reply. 

                   

                  If I am using the Dual function and tying to left join the table with month as the key field the values are duplicating.

                   

                  Can you suggest some other option..

                   

                  Regards,

                  Alvin.

                  • Re: Sort Inline Table
                    alvinford ford

                    Hi Sridhar,

                     

                    Thanks for your reply. 

                     

                    If I am using the Dual function and tying to left join the table with month as the key field the values are duplicating.

                     

                    Can you suggest some other option..

                     

                    Regards,

                    Alvin.

                      • Sort Inline Table
                        Sridhar Ethiraj

                        Hi Alvin,

                         

                        I hope the key field which you are trying to join is string field and the inline month is the Number.

                         

                        Are you extracting the month from a date field in you tarnsaction table?

                         

                        Could pls post a sample, so that it would be easy for us to give you the solution.

                         

                        -Sridhar

                          • Re: Sort Inline Table
                            alvinford ford

                            Hiii All,

                             

                            Please find the attched QVD. It has the following columns

                             

                            LOAD

                             

                            PRVT_STAFF_ID,

                            MTH_YR,

                             

                            Month,

                            Year,

                             

                            FACT_STAFF_LV1:

                            LOAD PRVT_STAFF_ID,

                                   MTH_YR,

                                 Month,

                                 if(Month='Jan',1,

                                    if(Month='Feb',2,

                                       if(Month='Mar',3,

                                          if(Month='Apr',4,

                                             if(Month='May',5,

                                                if(Month='Jun',6,

                                                   if(Month='Jul',7,

                                                      if(Month='Aug',8,

                                                         if(Month='Sep',9,

                                                            if(Month='Oct',10,

                                                               if(Month='Nov',11,12))))))))))) as MonthNO,

                                                                 

                                 Year,

                                 LV_TAKEN_CNT,

                                 LV_TYPE_SK,

                            //     LAST_UPD_DATE

                            FROM

                            C:\Users\Desktop\FACT_STAFF_LV1.Qvd

                            (qvd);

                             

                             

                            the problem is its not sorting as expected . Can any one help me to sort and achieve the cummulative result for the column LV_TAKEN_CNT.Plase ignore last_upd_date column.The main key field is PRVT_STAFF_ID.

                            Regards,

                            Alvin.

                             

                             

                             

                             

                             

                             

                             

                            LV_TAKEN_CNT,

                            LV_TYPE_SK,

                            FROM

                             

                            I want to calculate the Cummulative Values for the column LV_TAKEN_CNT so inorder to sort it i have created the following script.

                            D:\Users\FACT_LV.Qvd

                            (qvd);

                      • Re: Sort Inline Table
                        Sokkorn Cheav

                        Hi alvinford,

                         

                        Why you don't arrange it in load script?

                        load * inline [
                        Months,MonthNo
                        Jan,1
                        Feb,2
                        Mar,3
                        Apr,4
                        May,5
                        Jun,6
                        Jul,7
                        Aug,8
                        Sep,9
                        Oct,10
                        Nov,11
                        Dec,12
                        ];
                        

                        If you want to sort by [Months] then try this =Match(Months, $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))

                         

                        Regards,

                        Sokkorn Cheav