Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you change this comma to a &
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
Hi Sunny,
When I put the code it throws an error (see attached screen shot)
Thanks
Chris
Can you change this comma to a &
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
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