topic Model a 1:N relation in Qlik Compose

Model a 1:N relation

paolo_cordini — Fri, 14 Mar 2025 20:49:07 GMT

Hi,

I have a database designed in this way. The main "fact tables" has ID's fields pointing to "bridge" tables, in turn pointings to "subdetail" tables.

The relationship between the "main" table and the "subdetail" tables is 1:N

I am trying to figure out whether there Is a way to model such a relationship with Compose.

I cannot link the main table to "bridge" tables because the relation fields are not primary keys. I cannot define them as primary keys because they are not unique.

So I cannot use this relationship in Data Mart definition.

Thanks

Paolo

Re: Model a 1:N relation

Nanda_Ravindra — Fri, 01 Apr 2022 14:08:06 GMT

Hello @paolo_cordini ,

I am not seeing any direct way to model such a relationship with Compose. If you are still looking for a solution, please open a support case, and the team should be able to assist you if there are any workarounds.

Thanks,

Nanda

Re: Model a 1:N relation

TimGarrod — Fri, 01 Apr 2022 14:14:40 GMT

Hi, Can you provide some info on what you would want the "fact" table to look like?

This feels like a scenario where pivoting the data set to flatten the multiple values in the DW model may support what you are looking for - but would be good to understand output to be able to provide possible solutions.

Re: Model a 1:N relation

paolo_cordini — Tue, 05 Apr 2022 06:39:35 GMT

actually, I could say there are many fact tables. The table I called "Main Table" is a fact table but "Detail1", "Detail2", are fact tables too, with 1:n relationship with the main one.

I cannot pivot the data set because I don't know in advance how many rows are in "Detail" tables for one record in the Main Table.

The solution I adopted for the moment is to leave the tables unrelated in the data model. In the Data Mart I defined as "dimensions" (even if they aren't) the detail tables, so I could flatten "Bridge" and "Detail" but without linking them to the fact table.

It's ugly and non-canonical, but it works for the final users because the Main Table and Detail tables must remain separated.

Thank you

Paolo

Re: Model a 1:N relation

lyka — Thu, 07 Apr 2022 17:20:00 GMT

Hi Paolo,

It looks like you have found a solution based on your last comment. We suggest submitting a feature request thru our Ideation for your intended use case.

Thanks

Lyka