Difference Between Syn Table & Link Table

[#1]

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

[hic · Accepted solution]

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

[vikasmahajan]

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

[#4]

Thank you for your reply Vikas.

[#5]

Thank you so much Henric. I will have a look.

[#6]

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

[rwunderlich]

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

[#8]

Thanks Rob.