Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

Capture.PNG Capture 2.PNG

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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!