Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor III

## Creating Multiple Fact Tables with Shared Dimensions in Qlik Sense App for Filtering

How can I create multiple fact tables in one Qlik Sense app with shared dimensions for filtering, without displaying more than one fact field at a time? My goal is to be able to filter all facts by the common dimensions, but not to create any visualizations or tables with more than one fact field. What steps should I follow to achieve this in Qlik Sense?

I should mention that I don't like to joining two tables on a common field, since could result in a Cartesian product or a large number of duplicates if the common field is not unique.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
MVP & Luminary

The recommended approach is to create the data-model as star-scheme with just a single fact-table with n dimension-tables. The most tables respectively table-parts should be merged vertically by a concatenation with appropriate harmonizing of the field-names and field-values and the the others with joins and/or often better with mappings.

4 Replies
MVP & Luminary

The recommended approach is to create the data-model as star-scheme with just a single fact-table with n dimension-tables. The most tables respectively table-parts should be merged vertically by a concatenation with appropriate harmonizing of the field-names and field-values and the the others with joins and/or often better with mappings.

Contributor III
Author

May I ask you to explain with this sample:

fact1:
[dim_a, dim_b, dim_c
John, a, USA
Peter, c, UK];

fact2:
[dim_a, dim_b, dim_d, measure_1
John, b, abc,1
Mary, c, def,2
Mary, e, def,4
Peter, d, ghf,3];

when I show fact1 in a table, I see this:

Master

there are at least two ways to model this, the simplest is to concatenate these two facts into one table, but you may want to add a field that makes a disinction between the two data sets:

``````fact:
LOAD 'fact1' as dataSet, * INLINE
[dim_a, dim_b, dim_c
John, a, USA
Peter, c, UK];

concatenate (fact)
LOAD 'fact2' as dataSet, * INLINE
[dim_a, dim_b, dim_d, measure_1
John, b, abc,1
Mary, c, def,2
Mary, e, def,4
Peter, d, ghf,3];``````

if you want to create an expression for the first data set use the set analysis

``sum({<dataSet={'fact1'}>}...``

this way you are controlling which data set you wish to use

MVP & Luminary

Just follow the example from @edwin. It avoids trouble with synthetic keys and/or circular loops, provides the access to all exists data (which could be never ensured by joining/associating tables) and enables also the possibility to differentiate between the sources.

Beside this it's the most simplest and fastest approach to build a data-model and could be applied as general solution. All other ways are more complex and more complicated and are requiring much more knowledge and experience - without any guarantee to get a better working or more performant data-model.

Tags
Community Browser