Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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!