Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

MASTERTABLE (LINK TABLE or Concatenated FACT Table)

nick_scott
New Contributor III

MASTERTABLE (LINK TABLE or Concatenated FACT Table)

Automated Link Table

Automating parts of the Script can shorten the development cycle and add to consistency across applications.  The use of a Link Table works well with a data mart / enterprise data warehouse.  Automating the creation of the Link Table can be used to simplify the application data model build a few subroutines.

Data Model - Dimensional Model

A well-structured data model provides many advantages in QlikView application development, including extensibility, collaboration and governance.  A dimensional data model approach, with highly provisioned FACT & DIMENSION tables as promulgated by RALPH KIMBALL et al. provides an ideal data structure for most QlikView reporting / analysis applications.  This is a basic requirement for building a link table.

Link v. Concatenate Fact table

The choice between using a LINK TABLE or CONCATENATED FACT table will be influenced as much by the number of records in the data model as by the developers choice between using set analysis or flags in the chart expressions.

There is extensive information on application optimization, see: Wiley: QlikView Your Business, An expert guide to Business Discovery with QlikView and Qlik Sense and Packt: Mastering QlikView.

Automated Link Table

The automatic build of a Link Table need not be complicated.  It must, however, be predicated on some consistency in table / field naming (adopting Naming Conventions).  There are a number of  QlikCommunity applications which demonstrate the use of script to create a LinkTable.

See also:

Rob Wunderlich / QlikView Components: https://github.com/RobWunderlich/Qlikview-Components

QlikTips:  Rules for creating a Key/Link Table in QlikView: http://www.qliktips.com/2011/05/rules-for-creating-keylink-table-in.html

Automated Link Table Example

This is an illustration, building on that of Lars Christensen, (exSitec, l_konsult@hotmail.com), which demonstrates the LINK TABLE and CONCATENATED FACT Table approaches on a simple data set.

In Technical Content, the approach uses core QlikView Table Functions and Inter Record Functions to review the tables and fields.

Please post your comments,

Explore & Enjoy,

Nick Scott.

github.png

Discover more on GitHub: GitHub - nicholasscott72/Qlik-DimensionalModelling: A simple development methodology for QlikView ap...

Attachments
Comments
nick_scott
New Contributor III

It seems to be such a fundamental point in Qlikview development, but poorly addressed on the QlikCommunity: do you CONCATENATE or LINK.

Working with a complex data set requires a rigorous approach to primary & foreign keys and field naming.  This application shows that by following a "KIMBALL" dimensional modelling methodlogy either the LINKTABLE or CONCATENATED FACT table approach can be taken.

Ultimately, the choice seems to come down to what is easier for the develper to create & administer.  Using a script file to create the "mastertable" enables the choice to be made and performance difference to be evaluated.

What is certain is that aiming to create highly provisioned fact and dimension tables, following a "dimensional modelling" methodology, provides significant development advantages.

lornafnb
Valued Contributor

amazing piece of code. Thanks to all.

manojkulkarni
Valued Contributor II

very useful content & document. Thanks for sharing

gilbertomedeiro
New Contributor III

Congratulations Nicholas!

Thanks for this explanation. Articles like that, at least to me, constitute a rich learning experience and fortify the community.

lornafnb
Valued Contributor

Thank you so much

santiago_respane
Valued Contributor

Excellent! Thanks a lot!

Will try to apply in my solutions!

Version history
Revision #:
1 of 1
Last update:
‎10-27-2013 06:49 PM
Updated by: