Hi everyone, Yet another thread about synthetic keys. I could not find my answer in another thread, so here goes.
I have a table that contains the stock information of our products. These can be either used or non-used. I want to display the aggregated used and non-used stocks per product in my Qlikview file. I decided to do the aggregation in the script, causing both the raw stock table and the aggregated stock table to be in the data model. Qlikview then creates a synthetic key between these tables. Now my question is: Which of the following is usually best to do:
- Should I not create the aggregated table at all, and do the aggregation in the chart object using set analysis? This might make the report to become slower, right?
- Should I create a unique key with the productID and the used-property and put them in a link table between the raw and the aggregated stock tables?
Or are there better suggestions? Any document or information on this subject would be very welcome.
Thanks for your answer. I have read both these articles in other threads, and I understand the concept and that sometimes Synthetic keys are not a problem. I am just hoping that a more experienced Qlikview developer than myself can advice me on whether this would be such a case in which the synthetic key is the result of 'a poorly designed data model' or if it suits its purpose here.
I would recommend to do all possible aggression calculations/operations at script level only. Aggressions at front level/chart object is not good practice.
You can use link-tables or do aggressions separately for used items & unused items. And then concatenate both results in one table (make sure tables that will be concatenated should have same structure/fields/columns).
In front-level chart object you can directly display those values.