tl;rd: Yes, AutoNumber() created fields in QlikView script will save memory by omitting a symbol table. Surprisingly, storing these fields into a QVD file will create a symbol table for each field and take memory like every other field. If you do the full read on this post you will learn two other ways to create memory saving fields.
After a confusion I created with my partly false statement in Oleg's performance session at the QlikView Masters Summit in Amsterdam recently ;-) I decided to do a short dive into the matter to clarify and to show you some usable insights.
QlikView has the ability to recognize fields containing consecutive integer values and to optimize the symbols out. That means no symbol table is created in memory for the field, only bit stuffed indexes are used on a record level. So, it's a very good way to optimize your QlikView application.
Worth to mention here is that it doesn't really matter if the consecutive integer values are created by AutoNumber(), RowNo() or from an INLINE Load.
Here is a QlikView script to show the three different possible ways to create a symbol-less field (a key):
//AutoNumber() and RowNo() both create consecutive values and therefore a field w/o symbols (key):
Data1:
LOAD
RowNo() as rowno_key, AutoNumber(value) as autonumber_key, value;
LOAD
Rand() as value
AutoGenerate 100;
//This will break the autonumber_key (no consecutive value anymore):
//Concatenate LOAD 101 as rowno_key, Floor(Rand()*1000) as autonumber_key, Rand() as value
//AutoGenerate 1;
//This way (and only this way) can create a consecutive value key "manually" like with autonumber by using Floor():
Data2:
LOAD Floor(F1) as inline_key INLINE [
F1
1
2
2
3
4
4
4
5
6
];
//No Floor() used therefore no symbol-less key created (it even stores number format as string in QVD!):
Data3:
LOAD F1 as no_key1 INLINE [
F1
1
2
2
3
4
4
4
5
6
];
//Floor() used but no consecutive values therefore no symbol-less key created:
Data4:
LOAD Floor(F1) as no_key2 INLINE [
F1
1
2
2
2
4
4
4
5
6
];
The memory usage of the created fields you can see in the memory file:
As you can see we have three fields created where symbols take no memory. I wonder if this will be happen if we load a autoincrement field from a database. I will figure this out in a next post.
Download above QlikView example about AutoNumber: AutonumberTest.zip
