Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

Today I decided to blog about the Autonumber function that can be used to create a “compact memory representation of a complex key.”  Having recently learned about this function, I realized there have been times in the past when this would have been helpful to use.  For instance, when I need to build a link table in my data model, I often create keys that I use to link the tables.  Sometime these key fields are lengthy and are a combination of 3 or 4 fields.  In this blog, I will show you how you can use the Autonumber function to create a “compact memory representation of a complex key.”

 

Assume I load a data set that looks like this:

Data to load.png

And I want to load another data set that looks like this:

Data to load2.png

These two data sets have the same first four fields so if I were to load them as is, I would get a synthetic table in my data model.  To avoid that I will set up a key field in each of the tables that includes the FoodCategory, StoreNo, Year and Month fields.  This key field will be the field that links these two tables.  I will do this using a preceding load when I load both of these tables.  The script would look like this:

script.png

In the first table, I am using a preceding load to load all fields and then I am using the Autonumber function to create a key field that represents the four fields: FoodCategory, StoreNo, Year and Month.  I am doing the same thing in the second table I am loading but the difference here is that I am not loading the key fields.  By not loading the key fields, I am preventing a synthetic table from being loaded.  The end result looks like this:

table.png

Notice the FSYMkey field.  In this example, it is a unique integer that represents a larger expression.  In the past, I would have created the key field like this (see the FSYMkey2 field in the table below):

table2.png

FSYMkey2 is a more complex field that would have taken up more memory.  This example is small but if you had thousands of unique key fields like this, the consumed memory would add up.  By using the Autonumber function, I was able to use an integer to represent a long string thus minimizing the memory usage in my app.  This is one of many tricks that can be used to reduce the memory usage in your app.  Henric Cronstrom has some other ideas in his Symbol Tables and Bit-Stuffed Pointers blog.  Check it out.

 

 

Thanks,

Jennell

18 Comments
vikasmahajan

Nice

0 Likes
2,348 Views
Anonymous
Not applicable

Rob - you are correct.  I misunderstood it's function.  Thanks for correcting.

Kyle

0 Likes
2,348 Views
Anonymous
Not applicable

Rob,

"Remember to always use the second parm to autonumber -- the "autoid" to ensure your values are sequential when autonumbering more than one field. Otherwise you will lose the symbol table optimization."

If by Symbol Table Optimization you mean that the field value is not stored and only the pointer is, that's not true. I can only speak of V11SR12 QVW (classic). Using the AutoID parameter or not still maintains the optimization.

0 Likes
2,329 Views
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Steven,

The AutoID parameter is only important when autonumbering more than one field name. You can leave it off if only doing one field -- is that your case? 

-Rob

0 Likes
2,329 Views
Anonymous
Not applicable

Rob, I think we are getting our wires crossed. I am OK with the concept of the need for AutoID or not. I am responding to what I think is your assertion that the special optimization that AutoNumber has only happens when the AutoID parameter is used.

The special optimization being that if the output of AutoNumber is stored in a field then that field's symbol table only stores the pointer and not the value as well. That avoidance of storing the values as well saves memory in the symbols table area of memory.

Maybe I was connecting the logic dots incorrectly in your statement below:

"Remember to always use the second parm to autonumber -- the "autoid" to ensure your values are sequential when autonumbering more than one field. Otherwise you will lose the symbol table optimization."

Steven

0 Likes
2,329 Views
Anonymous
Not applicable

Joydip,

I recommend you do not use the QVW file size to give you an accurate insight memory usage. There is a standard feature of QVW that can do that far more accurately.

Use
QVW Desktop \ Document Properties \ General \ Memory Statistics

You can also use this feature combined with Rob Wunderlich's excellent Document Analyzer tool available at:

http://qlikviewcookbook.com/tools/#squelch-taas-accordion-shortcode-content-2

0 Likes
2,329 Views
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Steven,

"Otherwise you will lose the symbol table optimization".


The "symbol table optimization" (my made-up name) has nothing to do with AutoNumber per se. It's that a field containing only sequential integers does not require a symbol table, thereby saving some memory.

Recommending AutoID for AutoNumber is just a way to ensure that the output of AutoNumber will be sequential with no gaps. Gaps would likely occur if you autonumbered more than one field and omitted AutoID.

-Rob

2,329 Views
Anonymous
Not applicable

Ah got you now Rob. Yes AutoNumber, IterNo, RowNo, and RecNo falling into the same category regard to integer counters saving memory by not storing values. But, as you say, if something interferes with that +1 numbering strategy the memory saving technique does not apply.

0 Likes
2,329 Views