Databases Reference
In-Depth Information
Reducing storage requirements
While it is often tempting to include as much data as possible, this can also make
your data model more complex than it needs to be. Additionally, as QlikView uses
an in-memory database, it is also a good idea to not waste resources. RAM is still a
much scarcer resource than disk-based storage.
Using number-based key fields
When linking between tables using key fields, it is advisable to use numbers as key
values instead of text. The
AutoNumber()
script function can be used to generate
a unique integer value for an expression or compound key, thus compacting the
occupied RAM space.
Consider, for example, the following list of colors and their corresponding value
assigned by the
AutoNumber()
function. As each new value appears in the list, a
consecutive number is assigned. All subsequent appearances of the same value
will take the value assigned to the first instance. For example, all appearances of the
Blue
color have been assigned the number
2
, as it is the second value we encounter
on the list:
Color
AutoNumber(Color)
Red
1
Blue
2
Green
3
Blue
2
Green
3
Yellow
4
A second parameter can be specified to handle more than one counter in the same
script and indicate which one should be used to assign the values. For example,
AutoNumber(Color, 'Color')
would use a counter called
Color
. As QlikView is
very efficient at compressing sequential numbers, it is advisable to use a different
counter for each separate key.
It is important to note that the
AutoNumber()
function returns a number solely based
on the load order. Encoding the same value in different QVW files might return
different numbers. Therefore, it is not possible to use results of the
AutoNumber()
function sourced from multiple QlikView documents.
