topic Re: Creating Multiple Fact Tables with Shared Dimensions in Qlik Sense App for Filtering in App Development

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

parviz_asoodehfard — Mon, 24 Apr 2023 15:16:21 GMT

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.

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

marcus_sommer — Mon, 24 Apr 2023 15:47:27 GMT

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.

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

parviz_asoodehfard — Mon, 24 Apr 2023 20:06:08 GMT

May I ask you to explain with this sample:

fact1:
LOAD * INLINE
[dim_a, dim_b, dim_c
John, a, USA
Mary, b, Canada
Peter, c, UK];

fact2:
LOAD * 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];

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

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

edwin — Mon, 24 Apr 2023 20:33:04 GMT

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 Mary, b, Canada 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

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

marcus_sommer — Tue, 25 Apr 2023 07:01:48 GMT

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.