topic Re: Problem with assigning a numberic ID to YearWeek using AutoNumber() in QlikView

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

Wed, 05 Aug 2015 10:07:54 GMT

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

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

Gysbert_Wassenaar — Thu, 06 Aug 2015 08:13:55 GMT

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.

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

jonathandienst — Thu, 06 Aug 2015 08:39:48 GMT

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.

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

Fri, 07 Aug 2015 06:38:14 GMT

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!