Autonumber

[Anonymous]

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

[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

[Anonymous]

Hi Sunny,

When I put the code it throws an error (see attached screen shot)

Thanks

Chris

[sunny_talwar · Accepted solution]

Can you change this comma to a &

[Anonymous]

Yes, it works

I have added it to all the autonumbers in several sub routines I have and used a different number i.e ID1 ID2 ID3 etc.

Fantastic, been at this for 2 days.

Many Thanks

Chris

[jonathandienst]

Perhaps this is what you need for the two autonumber expressions?

AutoNumber("Result(TT)" & "Subject(TT)", '1-' & "Year(TT)" & "Primary(TT)" & "Basket(TT)") As PKRowNoTT,

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