Hey everyone, as a newbie... I was just looking through and learning about the use of Autonumber().
I didn't realize what it was doing until yesterday...and somehow missed the obvious (!?). I thought autonumber would create an index ... sort of a key/value table, where the key was a sequential number, and the value was the field value (from the field that was used with the autonumber() function). So, I thought something like:
AutoNumber(Lastname,'Lastname') As Lastname
... would generate this:
And then, when referenced (in a chart or table), the lastname would read "Smith" or "Redmond", etc. However, I discovered (much to my ignorance), that is it the number itself, not the name, that is shown in chart/table objects, etc. So "Smith" is shown as '1', "Redmond" as '2', etc.
Question1: What if the users need to see the value, instead of the number? Would it best to create a second table... as a reference for an autonumber field? For example, if I had a list of donations, in a table, called Gifts:
Original Load Script:
SQL Select …
I could change the Load Script to read as follows:
New Load Script:
,AutoNumber(GiftKey) As GK_Link
SQL Select …
DROP GiftKey from Gifts;
So the Autonumber field, GK_Link is really a new, optimized lookup table (and the field value, Giftkey, is moved to another table, called GiftKeys):
As far as the GiftKey, above...
- What if Giftkeys is referenced by other, multiple tables? Will this cause other performance issues? For instance, if a ‘Matching Gifts’ table needs to link to Gift dimensions (Gifts table) via the GiftKey field (see below).
- Also what if multiple expressions refer to the GiftKey in the Giftkeys table… [rather than in the Gifts Table, as previously?]. Will not additional hops be less efficient?
I wondered if this would be a performance improvement, or not? Would the GiftKeys table (optimized for minimal Symbol space…in terms of a sequential number, GK_Link) provide better efficiency/speed than originally? ….Even if the field values (Giftkey), now stored in another table, required the same amount of storage space in a symbol table?
I tested these question by running the numbers through Rob Wunderlich’s Document Analyzer. Here were the results]:
Original Results (Using GiftKey alone)
Modified Results (Generating GK_Link, Moving GiftKey to new reference table called GiftKeys)
My results? While I did reduce the Symbol width to 0, for the GK_Link, from the original Gifts table (now an autonumber field), I added the same number of rows/storage space in the reference table (GiftKeys) for the field values (GIftKey). In effect, I just added two million rows… and increased, not decreased, storage space, load-time, etc. [just the opposite of my goal]... [comment...now obvious, to a newbie like me].
Questions: So, is there a way to use autonumber (or similar), for any fields we need to keep, and reference the field values?
More importantly, Is there another way to create an index in Qlikview?
- Is there a “Create Index” mechanism in Qlikview (similar to databases such as Informix or MS SQL Server)?
- And would such indexes be more proficient regarding speed (less load time, fast calculation / recalculation times, etc.) - or would they just add additional storage space... for the same amount/number of rows from the original column?