topic Re: How to make AutoNumber() with group display continuously in QlikView

How to make AutoNumber() with group display continuously

Anonymous — Sun, 24 Mar 2013 17:59:45 GMT

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

Re: How to make AutoNumber() with group display continuously

Miguel_Angel_Baeyens — Sun, 24 Mar 2013 18:41:20 GMT

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

Re: How to make AutoNumber() with group display continuously

Anonymous — Sun, 24 Mar 2013 19:25:40 GMT

Miguel,

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

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

Re: How to make AutoNumber() with group display continuously

maksim_senin — Mon, 25 Mar 2013 03:06:19 GMT

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

Re: How to make AutoNumber() with group display continuously

Anonymous — Mon, 25 Mar 2013 03:59:30 GMT

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

Re: How to make AutoNumber() with group display continuously

maksim_senin — Mon, 25 Mar 2013 04:13:11 GMT

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

Re: How to make AutoNumber() with group display continuously

Anonymous — Tue, 26 Mar 2013 00:29:08 GMT

Maxim,

Thanks for the tip. That resolved the issue.

Re: How to make AutoNumber() with group display continuously

Thu, 16 Oct 2014 06:36:47 GMT

Where does the order by clause go in load statement

Re: How to make AutoNumber() with group display continuously

anbu1984 — Thu, 16 Oct 2014 06:51:35 GMT

Order by clause should be last in Load statement

Load * From Table

Where ...

Group By ..

Order By ..

Re: How to make AutoNumber() with group display continuously

Thu, 16 Oct 2014 07:25:33 GMT

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

Re: How to make AutoNumber() with group display continuously

anbu1984 — Thu, 16 Oct 2014 07:32:30 GMT

You cannot use Order by on QVD. Try like this

Temp:

Load *

from Data.QVD (qvd);

Final:

Load * Resident Temp Order By Field;

Drop table Temp;

From QV Help

order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national ASCII value. This clause may only be used when the data source is a resident table. The ordering fields specify which fields the resident table is sorted by. The field can be specified by its name or by its number in the resident table (the first field is number 1).

Re: How to make AutoNumber() with group display continuously

Thu, 16 Oct 2014 12:48:23 GMT

Say, I am using AutoNumber to assign a unique ID to the combination of Week & Year in two different tables. These Tables in the data model are not connected
.but still autonumber assigns the same id to the same combinations although these combinations might be coming from different tables. is this always the case?