Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
ArturoMuñoz
Employee
Employee

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!

23 Comments
rickwild
Partner
Partner

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.

10,000 Views
trishita
Creator III
Creator III

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

0 Likes
10,000 Views
steve_l
Contributor
Contributor
Hi all, great article but I've never been clear on a few related things. - What is the advantage of concatenating two fact tables rather than outer joining them on common fields? At that point doesn't it just become a star schema? - Why would you still need a link table when fact tables are concatenated? I would think this is an either/or situation but I still see them referred to as used together. Any references you folks can link to would be great. I can't view a qvw file. Thanks!
0 Likes
9,329 Views