Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
This is my 1st post in this community and 3rd day on Qlikview, So please don't mind if my question sounds doesn't make any sense.
I would like to know the difference between the Syn Table and Link Table.
I have create a model where I could see Syn table automatically created when I brought in a table which has 2 columns that have to joined to 2 different tables to bring uniqueness.
I will try to be more clear:
I have a fact table and three 3 dimensions joined to it .For Ex:
Fact |
---|
Date |
City |
Product |
Sales |
Location |
---|
City |
State |
Country |
Date |
---|
Date |
Month |
Year |
Product |
---|
Product |
Product Line |
Product Type |
So far it is good, now I have brought in a new table which has a measure in it but at different granularity like budget(As below).
Budget |
---|
City |
Date |
Budget |
When I reloaded the document after adding the Budget I found a Syn table acting as a link between newly added table or fact and to other dimensions.
I didn't find any issues with the results, When I created a chart list with product in it Budget repeats for every product which is fine.
I would like to know is it the right way to do it or I have to concatenate both the Fact and Budget tables or link both the tables using a link table.I haven't used either of the approach so far.
Please suggest me the best approach.I am happy to provide any additional information if required. Thanks in Advance.
-MC
You should concatenate your Fact table with your budget table. E.g.
Facts:
Load *,
'Actuals' as Type
From Facts;
Concatenate
Load *,
'Budget' as Type
From Budget;
Read more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity
HIC
You should concatenate your Fact table with your budget table. E.g.
Facts:
Load *,
'Actuals' as Type
From Facts;
Concatenate
Load *,
'Budget' as Type
From Budget;
Read more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity
HIC
I would like to know the difference between the Syn Table and Link Table.
What is Synthetic key and how to avoid it in QlikView
Ans : It is undesirable to have multiple common keys across multiple tables in a QlikView data structure. This may cause QlikView to use complex keys (a.k.a. synthetic keys) to generate the connections in the data structure. Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload anapplication. They also make a document harder to understand and maintain. Thereare some cases where synthetic keys cannot be avoided (e.g. Interval Match tables),but, in general, synthetic keys should always be eliminated, if possible.
1. comment the fileds in load script
2. rename the fileds in load script
3. rename the fileds using UNQUALIFY operator;
Q : What is Central Link Table
In the event of multiple fact tables QlikView In-Memory Technology allows us to create a central link table that only contains the existing data combinations. Instead of Joining the tables the event dimensions can be merged (CONCATENATED) in to one central Link table. This link table can then be linked back to the event measures one side and
the dimension tables on the other.
Coming to your problem find attached solution.
Hope this will help you to understand LINK TABLE
Vikas
Thank you for your reply Vikas.
Thank you so much Henric. I will have a look.
Hi Henric,
If I don't have Product in the chart list I am getting data as I expected but when I have Product Budget appears only at city level but I want Budget value to repeat for every product. I was able to able to achieve this with SYN table. How to do with Concatenation.
Thanks,SC
Conceptually, there is no difference between a Syn Key and a link table in your example. See this post for more:
http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys
-Rob
Thanks Rob.