8 Replies Latest reply: Jan 4, 2017 10:33 PM by Supriya R RSS

    Fetch Data according to sorting

    Supriya R

      Hi,

       

      I have below Script

       

      HelpRequetData:

      LOAD `ticket_id`,

      `user_id`  ,

          `date_created` as Help_request_Date;

      SQL SELECT `ticket_id`,

          `user_id`,

          `date_created`

      FROM HELPREQUESTS WHERE date_created >= '$(vWeekStart)' and date_created <= '$(vWeekEnd)' ;

       

      Left Join(HelpRequetData)

      LOAD

      `user_id` ,

          `client_id`;

      SQL SELECT `user_id`,

          `client_id`

      FROM USERS;

       

      LOAD  `client_id`,

          `client_name`;

      SQL SELECT `client_id`,

          `client_name`

      FROM CLIENT ;

       

      here i need to fetch data like it has to be sorted in client Name Wise alphabetically. As shown below

       

      user_id   client_id   client_name

      2              1              Abacus

      4               3             matie

      5              5              sjila

      6              4              jaimon

      710           2             oreal

      34            1              Abacus

      678            3             matie



      For above i need it to show like below


      user_id   client_id   client_name

      2              1              Abacus

      34            1              Abacus

      6              4              jaimon

      4               3             matie

      678            3             matie

      710           2             oreal

      5              5              sjila


      so that when where i retrieve user_id   it has to be order by client_name wise.



      How could i sort it? Please help on this.

       

       

       

       

       

       



        • Re: Fetch Data according to sorting
          Ganesh Reddy

          Hi Supriya,

           

          Try by adding below script,

           

          Noconcatinate

          tmp_HelpRequetData

          Load

          user_id ,

          client_id   ,

          client_name  

          Resident HelpRequetData

          order by client_name asc ;

           

          Drop table HelpRequetData;

           

          Rename table tmp_HelpRequetData to HelpRequetData;

           

          Thanks,

          Ganesh

          • Re: Fetch Data according to sorting
            Vineeth Pujari

            Hi

             

            Please post the complete script where you load CLIENT table. also post complete error message.

             

            The Client_Name field is relevant to CLIENT table so looking at load scripts of other tables is irrelevant.

             

             

            For Fixing the sort Order refer the attached app

             

            temp:

            Load * Inline [

            user_id,client_id,client_name

            2,1,Abacus

            4,3,matie

            5,5,sjila

            6,4,jaimon

            710,2,oreal

            34,1,Abacus

            678,3,matie];

             

            NoConcatenate

             

            FACT:

            LOAD * , RowNo() as SortORder

            Resident temp

            Order by client_name;

             

            drop table temp;

             

             

            Now Use Sort by Expression >> SortOrder

            sortorder.PNG

              • Re: Fetch Data according to sorting
                Supriya R

                Hi,

                 

                See my original post. It has three tables with a left join and then i have to ordering  according to client in script itself.

                I should not use sort expression.

                 

                How could i join tables with ordering. so i get user id according to client name order.

                 

                Please help me on this.

                  • Re: Fetch Data according to sorting
                    Vineeth Pujari

                    temp_HelpRequetData:

                    LOAD `ticket_id`,

                    `user_id`  ,

                        `date_created` as Help_request_Date;

                    SQL SELECT `ticket_id`,

                        `user_id`,

                        `date_created`

                    FROM HELPREQUESTS WHERE date_created >= '$(vWeekStart)' and date_created <= '$(vWeekEnd)' ;

                     

                    Left Join(temp_HelpRequetData)

                    LOAD

                    `user_id` ,

                        `client_id`;

                    SQL SELECT `user_id`,

                        `client_id`

                    FROM USERS;

                     

                    Left Join(temp_HelpRequetData)

                    LOAD  `client_id`,

                        `client_name`;

                    SQL SELECT `client_id`,

                        `client_name`

                    FROM CLIENT ;



                    HelpRequetData

                    Load  * ,Rowno() as SortOrder

                    RESIDENT temp_HelpRequetData

                    Order by client_name,Help_request_Date;

                     

                    Drop Table temp_HelpRequetData;