Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

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
3 Replies

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

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
MVP
MVP

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

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

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

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!

Community Browser