Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Concatenate vs Link Table

Are you stuck with a complex data model? Do you need some tips to resolve the linkage of different fact tables? Does your model have data knots, circular references or synthetic keys?

If you have ever faced any of these situations you've probably heard about Link Tables and Concatenate as methods that can help you to put some light in your data model. It’s hard to find a general rule to determine when you should Concatenate rather than joining tables by a Link Table so today we will highlight the main differences of both methods by reviewing usage case scenarios.

Lets start with some basic examples:

Concatenate

“This statement forces concatenation with an existing named table or the latest previously created Logical Table. A concatenation is in principle the same as the SQL UNION statement, but with two differences: first that Concatenate prefix can be used no matter if the tables have identical field names or not; and secondly that no removals of identical records are made”

Source: QlikView help

Most basic example of Concatenate usage could be when you need to merge two or more tables that have identical structures, let say you have a historical data warehouse with sales from 2005 to 2012 and then another table with 2013 sales live in you transactional database. To create a visualization of sales trends over the years you will want to have everything normalized in one fact table, Sales table, with the data from 2005 to 2013.

concatenate.png

Link Table(s)

“(…) a junction table is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, Link Table, pairing table, pivot table, transition table, or association table. (…).”

Source: Wikipedia

This definition is mostly about relational SQL databases so if we adapt it for QlikView it could be something like the following.

Link Table: It´s a table that contains common fields from two or more tables (within the same database or not). Easy, right?

The most common scenario for using Link Tables will be to replace synthetic keys and to avoid circular references by joining two or more fact tables against a common set of dimensions.

linktable.png

See much more in the attached files.

AMZ

Enjoy Qliking!

22 Comments
paulyeo11
Valued Contributor II

Hi Sir

Nice article. I happen to stuck with how to add sales table to my existing GL_TABLE , i cannot able to get it work. may be you can advise me where i go wrong ?

http://community.qlik.com/message/348362#348362

0 Likes
67 Views
barryharmsen
Contributor II

My rule of thumb is:

  • Use a link table when possible, as it keeps your data model more understandable and creates less 'hassle' on the front-end

  • Move to a concatenated fact table when data volumes increase to a level where performance becomes an issue.
67 Views
Not applicable

Hi, friend.

I'm starting developing with QlikView, and I have a little doubt for which you may give me a hand.

Suppose that we have two tables(t1, t2), and that they have a column called in the same way: 'ID', so they are going to be automatically linked by QlikView on Column ID, right?

So then, I create a table selecting the field ID (that has been automatically linked), and the question is: what IDs are going to be shown? I see that it's not an union between the ID of the two tables; just appear the IDs of one table, but I don't understand why.

Thanks in advance,

Lautaro.

0 Likes
67 Views
Not applicable

Hi,

Why not just join the two fact tables directly?

0 Likes
67 Views
Not applicable

"so they are going to be automatically linked". Try to go to the table viewer and see what happens

0 Likes
67 Views
miguelbraga
Valued Contributor III

This as a great article that helped me a lot in the past

0 Likes
67 Views
kidbank3
Contributor III

Arturo,

Why is the product table not in the data model on page 10 of your document?  Is that an error or am I misunderstanding the data model?

Thanks,

Mike

67 Views
Employee
Employee

I think you got it right, for coherence it should be there, however having product table in there wouldn't change anything after page 10.

Thanks for the heads-up

0 Likes
67 Views
kidbank3
Contributor III

Thanks for confirming

0 Likes
67 Views
gilbertomedeiro
New Contributor III

Congrats Arturo!!

Articles like that is quite easy to understand and also easy to apply.

Thanks

0 Likes
67 Views
Not applicable

Very nice, thanks specially for the attached document.

0 Likes
67 Views
beck110979
Valued Contributor III

Thanks for sharing

0 Likes
67 Views
william_erickss1
New Contributor III

Dear Arturo,

Is there a rule of thumb regarding total qvd file size, number of rows, or number of columns that would suggest it would be preferable to implement a concatenated fact table rather than a link table for a data model with multiple fact types and conformed dimensions?

Thanks,
Will

0 Likes
67 Views
paulyeo11
Valued Contributor II

Hi William

File size is depend on the project. You can have many row & column also depend on the project .

Better to have link table with many fact table.

Dimension also can have many depend on your project.

But if your GUI have too many sheet , it may affect loading time when open QV.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

0 Likes
67 Views
Employee
Employee

Hi William,

Not really there's no hard limits here. As stated in the pdf performance gains can be observed when concatenating large tables. In complex data models hybrid solutions are often the way to go.

I particularly always start my data modeling with concatenation because it's simpler and most of the times does the job. If the business use case (extensive use of set analysis is an issue) or if the data model requires it (data at different levels of granularity) I work on link tables as well.

0 Likes
67 Views
renjithpl
Valued Contributor

link_fact_1.jpg

My both fact tables link to the Link table using Key1, In this case is it good to concatenate it in one table,

as both facts have huge records and different fields.

With current data model i am facing a lot of frontend performance issue.

0 Likes
67 Views
trishita
Contributor

SYNTHETIC KEY.PNGI want that there is only one link between calender and generalinformation table which would be survey_date

0 Likes
67 Views
datanibbler
Esteemed Contributor

Hi Arturo,

I find your post very interesting indeed. We have some reports already finished here and all of them have a link_table in the DataModel. The datamodels are very complex, there are a lot of tables linked to the linktable, each one with a different key, some tables even have several different keys depending on the type of document ... because of that, we don't always have a 1:n relationship. I'm maybe going to amend that when I have the time - data is only aggregated on the GUI, so it doesn't really matter (as of now) whether a key links to 1 or to 3 records, but it just looks a lot more orderly when the key is unique everywhere ... That will mean I'll have to split some tables into several parts.

0 Likes
67 Views
rickwild
Contributor
Hi Trishita,

If you remove SURVEY_DATE_NUM from the table General_Information, the synthetic key will disappear, and the tables will associate using SURVEY_DATE only..

Note I would use SURVEY_DATE_NUM as the key, as I have sometimes had trouble matching date formats between tables.

So, have the field SURVEY_DATE_NUM in General_Information and in Calendar, and have the field SURVEY_DATE only in the Calendar.

0 Likes
67 Views
trishita
Contributor

rickwild‌ When I tried to remove the survey_date_num from the load script of general informations and tried to load the script it failed to load and showed an error

Field not found error

Field 'SURVEY_DATE_NUM' not found.

The problem is I cannot  keep SURVEY_DATE only in the Calendar.This is because I am using SURVEY_DATE IN General Informations and SoundingCorrections table to join them on the basis of interval Match(SURVEY_DATE,IMO_NO)

0 Likes
67 Views
rickwild
Contributor

Hi Trishita,

I don't know your script, so I don't know what is causing the error. It doesn't matter really which of the fields you use to associate GENERAL_INFORMATIONS and CALENDAR. If both fields are in both tables, you will get a synthetic key. That's just how it works. In this case it probably doesn't hurt, if any SURVEY_DATE always corresponds to the same SURVEY_DATE_NUM.

67 Views
trishita
Contributor

Thank you for the idea.I think I understand what you meant

0 Likes
67 Views