Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.
You can use generic keys to solve many data modeling problems:
See more in the attached files.
PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.
It is just a mock-up script, so it is not much to see. I can make you a better example, but I don't have time for it the nearest weeks. I'll post something here in a while.
HIC
I don't understant what's the '<ANY>' field for. Can you help me with a deeper explanation?
Thanks a lot!
'<ANY>' is a field value that is made to link to all real-world field values. It is really the core of Generic Keys - what makes them useful.
HIC
So, should I create that '<ANY>' field wherever I need it to create the keys?
The document describes where and how you should create a generic key. A very simple example is if you have a fact table where some records link to product 1, some to product 2, and yet some to any product. In such a case, you should link the fact table with the products table using the following table:
HIC
Hi HIC, a great job! One question regarding the memory consumtion:
I have two keys 1234567 and 7654321. Right now I'm connecting them through an underline. But somehow I have the feeling that it is quite hungry regarding the memory.
I thought first to connect the two num with an &, but theoretically this could lead to a duplicate.
I saw you're using auto number. Can I use it for incremental loads also?? So
when I have twice this numbers 1234567 and 7654321 - everytime the autonumber will create the same key, right?
Thanks in advance
//chesterluck
Autonumber will create the same key - but only when used within the same script run. Since your incremental load probably involves several script runs, you can probably not use Autonumber.
So an underline may still be the best approach.
HIC
That doesn't sound good . I tested it and the underline is consuming a lot of memory. Are there any other approaches? What is if I connect the two numbers with two 00 inbetween? IDnum1&'00'&IDnum2
1234567&'00'&7654321
This way I'm sure that the first num can't get so big (as it strarts with 6 dig) and the second cant start with 0.
What do you think?
cheers chesterluck
If you know that the two numbers always each have exactly 7 digits or less, then I would suggest
Key1*10000000 + Key2 as NewKey
The result will be an integer that QlikView can manage and that will use less memory than a string. QlikView can handle any integer with 14 digits or less.
HIC
Thats a nice idea. So basically
Key1 * 10^(Max(Digits)) + Key2 as NewKey
why actually '+ Key2' and not
(Key1 * 10^(Max(Digits)) & Key2)*1 as NewKey
?