How to make AutoNumber() with group display continuously

[Anonymous]

I am using AutonNumber() with groups to create my serial values to using in future calculations.  The grouping are working but the numbers skip the next values as seen below.

Notice below that MyWeekSerial continues sequencially.  Week 4 then 5 then 6 but my month goes from 2 to 7 instead of 2 to 3. 

Can anyone help with this?  Thanks

Load   
    DayID
    Year(DayID)    AS MyYear,
    Week(DayID)    As MyWeek,
    Month(DayID)   as MyMonth,
    Day(DayID) as myDay,
    AutoNumber(Year(DayID) & Week(DayID))    AS MyWeekSerial,
    AutoNumber(Year(DayID) & Month(DayID))    AS MyMonthSerial

From myCalendar;

Returned values are:

DayID           MyWeek      MyMonth      MyWeekSerial      MyMonthSerial

1/18/2013      3                Jan                4                           2

1/19/2013      3                Jan                4                           2

1/20/2013      3                Jan                4                           2

1/21/2013      4                Jan                5                           2

1/22/2013      4                Jan                5                           2

1/23/2013      4                Jan                5                           2

1/24/2013      4                Jan                5                           2

1/25/2013      4                Jan                5                           2

1/26/2013      4                Jan                5                           2

1/27/2013      4                Jan                5                           2

1/28/2013      5                Jan                6                           2

1/29/2013      5                Jan                6                           2

1/30/2013      5                Jan                6                           2

1/31/2013      5                Jan                6                           2

2/1/2013        5                Feb                6                           7

2/2/2013        5                Feb                6                           7

[Miguel_Angel_Baeyens · Accepted solution]

Hi Darrin,

Try assigning a label for each function of AutoNumber():

AutoNumber(Year(DayID) & Week(DayID), 'WeekSerial')    AS MyWeekSerial, // see the second parameter in function
AutoNumber(Year(DayID) & Month(DayID), 'MonthSerial')    AS MyMonthSerial

Hope that helps.

Miguel

[Anonymous]

Miguel,

Thank you so much for the quick response and correct answer.

I don't understand how the second parameter makes the change though.

[maksim_senin]

Hi Darrin,

The second parameter of AutoNumber() allows you to have several (more than one) counters in your app, in your example (before changes proposed by Miguel) combinations of Year&Week and Year&Month have the same counter, i.e. each unique combination increases the counter by 1, that is why you get 7 after 2 in MyMonthSerial.

As for QlikView documentation, sometimes it helps, sometimes it has a lack of additional information (examples) for understanding. For the 2nd parameter of AutoNumber() the help says "In order to create multiple counter instances if the autonumber function is used on different keys within the script, an optional parameter AutoID can be used for naming each counter." - IMHO it's not enough to deeply understand this useful feature. Thanks to Qommunity!

Best regards,

Maxim

[Anonymous]

Maxim, Thank you very much.

You are very correct.  Sometimes the documentation is great and other times it leaves you with more questions.

Like this one.

Why does the number 1 in my series display at the end.  The highest or last value. 

Thanks

[maksim_senin]

Darrin,

I'm sorry I don't understand your last words regarding "number 1", but I assume you're talking about the sequental numbers assigned with AutoNumber() to your combination(s), if so, may be it's worth to play with ordering, i.e. add "Order By" clause to your Load statement.

Best regards,

Maxim

[Anonymous]

Maxim,

Thanks for the tip.  That resolved the issue.

[#8]

Where does the order by clause go in load statement

[anbu1984]

Order by clause should be last in Load statement

Load * From Table

Where ...

Group By ..

Order By ..

[#10]

Does this work even if I am loading from a qvd in my load statement. I am getting an error during reload