Problem with assigning a numberic ID to YearWeek using AutoNumber()

[#1]

Hi,

I am trying to assign each of the YearWeek a new numeric ID using the Autonumber() function in the script, as shown below:

[Master Calendar]:
LOAD *,
  AutoNumber(YearWeek, 'YearWeekID') as [YearWeekID]
  ;
LOAD Timekey as Date, 
     Year, 
     Month, 
     WeekMethode, 
     Week, 
     WeekDay, 
     Day, 
     YearWeek, 
     YearMonth, 
     YearQuarter, 
     Quarter, 
     Period, 
     PeriodWeek, 
     FiscalWeek, 
     FiscalQuarter, 
     FiscalYear

FROM
.

After I created a temporary table box with the YearWeekID and YearWeek field to test whether the script is working as expect. However to my surprise I got the following result:

Shouldn't the calender be populated in ascending order in such a way that each new YearWeek will have a YearWeekID value that is the consecutive to its previous period?

Any help would be appricated

[Gysbert_Wassenaar · Accepted solution]

Shouldn't the calender be populated in ascending order in such a way that each new YearWeek will have a YearWeekID value that is the consecutive to its previous period?

Not unless your source data is sorted correctly already or you force that sorting in a resident load with an Order By clause.

[jonathandienst]

Autonumber does not sort the data, it increments the autonumber when a new value is encountered in load order. Sorting the data before the autonumber will ensure that it is numbered in order.

[#4]

Thanks guys that was really helpful. I made use of a resident table in order to sort the master calendar data before using the autonumber function!