11 Replies Latest reply: Jul 21, 2016 8:17 AM by Christopher Ellis RSS

    RowNo not reseting

    Christopher Ellis

      Could anyone explain how to reset the RowNo after concatenating 2 tables.

       

      I am using the RowNo as a filter and need it to reset after pushing 2 tables together.

       

      Any help appreciated.

       

      Many Thanks

       

      Chris

        • Re: RowNo not reseting
          Sunny Talwar

          It is not resetting? Can you elaborate with an example and also share the script that you are using to concatenate?

            • Re: RowNo not reseting
              Christopher Ellis

              Hi Sunny,

               

              The table created in step 3 called TMP4 keeps hold of the original RowNo that I want to reset.

               

              The process is repeated with different filters on the basket in step 1 creating a TMP4 table that is concatenated to.

               

              Step 1.) Filter resident table Assessment to only list Basket 1 into table named TMP

               

              TMP:

              LOAD

                  *,

              Resident Assessment

              Where ((([Category name])='Assessment') AND ((Basket)=1));

               

              Step 2.) Use Resident TMP table to find the RowNo that are below 2.

               

              Basket1:

              NoConcatenate

              LOAD *,

              Where RowNo < 2;

              LOAD *,

                AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

              Resident TMP

              Order By [P8 Points] desc, [Primary],[Subject],[Resultset],[PU],[Basket] desc;

               

              Step 3.) Use the TMP table to find records above 1 and store in TMP4 for later

               

              TMP4:

              LOAD *,

              Where RowNo > 1;

              LOAD *,

                AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

              Resident TMP

              Order By [P8 Points] desc,[Primary],[Subject],[Resultset],[PU],[Basket] desc;

               

              DROP Table TMP;

            • Re: RowNo not reseting
              Marcus Sommer

              Rowno() will count over all merged tables. If you want a separate counter per table then use Recno().

               

              - Marcus

                • Re: RowNo not reseting
                  Christopher Ellis

                  Hi Marcus,

                   

                  Cant get this to work.

                   

                  To try to clarify a bit , the tables that are concatenated are holding onto the RowNo they where originally given. I want to use the concatenated table with a reset RowNo.

                   

                  LOAD *,

                    AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

                  Resident TMP4

                  Order By [P8 Points] desc,[Primary],[Subject],[Resultset],[PU],[Basket] desc;

                   

                  Thanks

                   

                  Chris

                    • Re: RowNo not reseting
                      Marcus Sommer

                      I think your naming the field RowNo was a bit misleading and I'm not really sure if I understand what do you want to do but I believe that your load-structure with preceeding loads might not quite suitable. I think your load should be more look like this:

                       

                      table_0:

                      LOAD *,

                        AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

                      Resident Assessment

                      Where ((([Category name])='Assessment') AND ((Basket)=1))

                      Order By [P8 Points] desc, [Primary],[Subject],[Resultset],[PU],[Basket] desc;

                       

                      table_1:

                      Noconcatenate LOAD * resident table_0 Where RowNo < 2;

                       

                      table_2:

                      Noconcatenate LOAD * resident table_0 Where RowNo > 1;

                       

                      drop table table_0;

                       

                      - Marcus

                        • Re: RowNo not reseting
                          Christopher Ellis

                          Hi Marcus,

                           

                          The code you have above has helped me structure my code better thanks.

                          I am still getting the problem of the rowno not resetting.

                           

                          If I do the same code for the next basket and concatenate table_0 to table_2 the rowno stays the same. I need it to reset because I am using it further on.

                           

                          Many Thanks

                           

                          table_0:

                          LOAD *,

                            AutoNumber([P8 Points]&[Subject],[Primary]&[PU]&[Basket]) as RowNo

                          Resident Assessment

                          Where ((([Category name])='Assessment') AND ((Basket)=1))

                          Order By [P8 Points] desc, [Primary],[Subject],[Resultset],[PU],[Basket] desc;

                           

                          table_1:

                          Noconcatenate LOAD * resident table_0 Where RowNo < 2;

                           

                          concatenate (table_2) LOAD * resident table_0 Where RowNo > 1;

                           

                          drop table table_0;

                    • Re: RowNo not reseting
                      Christopher Ellis

                      Final figured it,

                       

                      Dropping the field RowNo and recreating it has sorted it and doing some reworking of the code based of some of your suggestions.

                       

                      Many thanks all for your help.

                       

                      Regards

                       

                      Chris