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.
Thank Henric
Using the combination of UniqueID &' - ' & Department &' - ' & User/SalesPerson as the MasterKey- I can compare all sorts of unrelated dimensions from leads, bookings, calls, web, budgets, PPC expenditure across year, quarter, month, day by attaching a separate Master Cal to each date.
WOW - Powerful insight!
Hi,
i have 5 levels of different authorization:
Do I need to combine all possible combination in the authorizationbridge?
This will end up with over 30 resident load from my fact table, or is there some other way doing this?
If you have a well behaving hierarchy, you only need to use the hierarchy node. See Authorization using a Hierarchy. But if you have additional fields, independent from the hierarchy, you need to combine these with the hierarchy node.
I don't know your data, but from your example it looks as if Business-Division-Section-Department is a hierarchy, and Cost is an independent field.
HIC
Hi Henric,
I have used your post Basics for complex authorization,its working on desktop not on access point
Section Access;
LOAD UPPER(ACCESS) as ACCESS,
UPPER(NTNAME) as NTNAME,
UPPER(Domain) as NTDOMAIN,
REPORTING_OFFICE &'|'& LOCAL_BRANCH_ANALAYSIS_CODE as %AuthID
FROM
(ooxml, embedded labels, table is Users);
Section Application;
-----------------
//Bridge Table used for Section Access
Bridge:
Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as %AuthID From $(HF_OGIS_QVD)(qvd);
Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, [Rep-OffOO] &'|'&'<ANY>' as %AuthID From $(HF_OGIS_QVD)(qvd);
Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, '<ANY>'&'|'& [Loc-Br-AnalOO] as %AuthID From $(HF_OGIS_QVD)(qvd);
Load [Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID From $(HF_OGIS_QVD)(qvd);
---
LOAD [Rep-OffOO] as [Reporting/Issuing Office],
[Rep-OffOO] &'|'& [Loc-Br-AnalOO] as AuthID,
[Cur-CdOO] as [Currency Code],
[OrgOO] as [Organization Code],
[Mgr-CdOO] as [Manager Code],
[Maj-LinOO] as [Major Line],
[Min-LinOO] as [Minor Line],
RowNo() as RowNumber
FROM
QVD
(qvd);
-------------
OGIS_DATA_Users1.xlsx- data
ACCESS NTNAME REPORTING_OFFICE LOCAL_BRANCH_ANALAYSIS_CODE DOMAIN
ADMIN R1-CORE\ADMIN <ANY> <ANY> R1-CORE
USER R1-CORE\abc <ANY> NA R1-CORE
Without the data, it is difficult to see what goes wrong. But I can give you a couple of suggestions:
HIC
Thanks for your reply
Change %AuthID to upper case-this is applicable for section access and bridge table both
yes i have tested with this USER R1-CORE\abc <ANY> NA R1-CORE and strict exclusion
NTNAME am using R1-CORE\ABC
Sample data
[Rep-OffOO] [Loc-Br-AnalOO]
123 00
144 NA
772 01
832
Hello Henric,
My goal is to link BoM data to Inventory and Sales data. I've also read your brief on creating a BoM but I won't be applying that detail to my model quite yet.
I've posted this question on the community forum twice now and still haven't found a solution. I believe your experience with Generic Keys in combination with a Link Table may be the solution but I'm unsure on how to implement it. It may be related to your Example 3 in the Generic Keys brief but I have been unable to mimic that method.
My issue when using just a link table is that not all facts keep/ have relationships to each other. For example, if a Product is selected, I want to have a graph that shows the inventory for that product over time with respect to the inventory locations and another graph that shows the inventory of the parts that make up this product over time with respect to the inventory locations. Currently, with just a Link table, when a product is selected, I do get both graphs as mentioned, however, if I select a specific date, or select a specific location, the graphs no longer show data due to an incorrect connection in the data.
Side Notes:
• The characteristics of a part is dependent on its location (the same part in two different locations can have a different "procurement type").
• The BoM of a product is dependent on its location (the same product in different locations can have different parts that go into it as well as different costs)
If you have time, I would GREATLY appreciate any insight you have to help me correctly link this data.
Here is a link to my latest post with example data to be connected:
Thank you for your time and I hope to hear from you!
Best,
Drew
Hi Heinrich,
I,ve just created a data model with generic keys to manage to different facts table (meetings and calls in a pharmaceutical company).
I would like to know if it's normal that when I select a month for the meeting (I have two different time tables, one for meeting and one for calls) I have all the list of available meeting and not only meetings done in the selected period.
It seems that using this data model qlik doesn't shoe only selection available after other selection, because generating generic keys, all data are always available. Isn't it?
Bets regards,
andrea
hic
I hope you can help me with this question:
If I take your pharmaceutical example, and tweak it: Product Category > Product > Packages.
Assuming a product can have one or more different packages.So if you sell a product you actually sell all the packages.
Let's assume the Sales fact table has Product ID only. I would like to know which packages were sold.
Is the Generic Key concept still to be used?
That's in order to avoid creating a transaction for each package.
In my case it will save tens millions of records.
Thank You!
If you want to be able to break up the numbers per package, you will need to have PackageID in your fact table, even if it means tens of millions of additional records.
The alternative is to reduce the grain in the fact table: Remove the PackageID and use ProductID as the finest granularity in the product hierarchy.
HIC