5 Replies Latest reply: Oct 20, 2017 9:00 AM by Jonathan Dienst RSS

    Autonumber

    Christopher Ellis

      I am using Autonumber to rank several fields but want to reset the Autonumber back to 1.

       

      If I load a table as below set an autonumber and then drop the PKRowNoTT field to use it again should this not reset the autonumber back to 0 ? if not can it be ?

       

      Sub TeacherTargetData

       

      //I load a table from SQL Server and set the first autonumber field

       

      LIB CONNECT TO 'ProgressTransitions';
      NoConcatenate
        TeacherTargets: 
        LOAD Distinct
            "Primary(TT)",
            "Result(TT)",
            "Year(TT)",
            "Subject(TT)",
            "Type(TT)",
            "Area(TT)",
            "Basket(TT)",
            "Key",
            AutoNumber("Result(TT)"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)") As PKRowNoTT,
            "Primary(TT)"&"Year(TT)" As TA8;
        SQL SELECT "Primary(TT)",
            "Result(TT)",
            "Year(TT)",
            "Subject(TT)",
            "Type(TT)",
            "Area(TT)",
            "Basket(TT)",
            "Key"
        FROM ProgressTransitions.dbo."view_teachertargets"
        Order by "Result(TT)" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;   

      DisConnect;

       

         

      //I use the PKRowNoTT to filter out records to be used in the next stage

       

          Left Join(TeacherTargets)

          Load Key,

              "Result(TT)" As tmpB4TT     

          Resident TeacherTargets

          Where (("Basket(TT)"=4)

          or ("Basket(TT)"=1 And PKRowNoTT>1)

          or ("Basket(TT)"=2 And PKRowNoTT>1)

          or ("Basket(TT)"=3 And PKRowNoTT>3));      

         

      //I drop the field to use it again

       

          Drop Field PKRowNoTT;     

       

      //I join the table back on to its self but with a new autonumber to allow me to filter out the records

       

          Left Join(TeacherTargets)

          Load 

              *,      

             AutoNumber("tmpB4TT"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)") As PKRowNoTT 

          Resident TeacherTargets

          Where tmpB4TT >=0.00

          Order by "tmpB4TT" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

       

      End Sub

        • Re: Autonumber
          Sunny Talwar

          Try this

           

          Sub TeacherTargetData

           

          //I load a table from SQL Server and set the first autonumber field

           

          LIB CONNECT TO 'ProgressTransitions';
          NoConcatenate
            TeacherTargets: 
            LOAD Distinct
                "Primary(TT)",
                "Result(TT)",
                "Year(TT)",
                "Subject(TT)",
                "Type(TT)",
                "Area(TT)",
                "Basket(TT)",
                "Key",
                AutoNumber("Result(TT)"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)", 'ID1') As PKRowNoTT,
                "Primary(TT)"&"Year(TT)" As TA8;
            SQL SELECT "Primary(TT)",
                "Result(TT)",
                "Year(TT)",
                "Subject(TT)",
                "Type(TT)",
                "Area(TT)",
                "Basket(TT)",
                "Key"
            FROM ProgressTransitions.dbo."view_teachertargets"
            Order by "Result(TT)" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

          DisConnect;

           

           

          //I use the PKRowNoTT to filter out records to be used in the next stage

           

              Left Join(TeacherTargets)

              Load Key,

                  "Result(TT)" As tmpB4TT

              Resident TeacherTargets

              Where (("Basket(TT)"=4)

              or ("Basket(TT)"=1 And PKRowNoTT>1)

              or ("Basket(TT)"=2 And PKRowNoTT>1)

              or ("Basket(TT)"=3 And PKRowNoTT>3));  

           

          //I drop the field to use it again

           

              Drop Field PKRowNoTT;

           

          //I join the table back on to its self but with a new autonumber to allow me to filter out the records

           

              Left Join(TeacherTargets)

              Load

                  *,  

                AutoNumber("tmpB4TT"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)", 'ID2') As PKRowNoTT

              Resident TeacherTargets

              Where tmpB4TT >=0.00

              Order by "tmpB4TT" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

           

          End Sub