Prevent association without renaming fields?

[greend21]

As the title says, is there a way to prevent automatic association without renaming fields? I want my data model like the PowerBI example below. Basically, the four tables in the middle of the image should filter the outside data sets. This allows me to use the same filter boxes across the application. I get synthetic keys when trying to do the same in Qlik Sense. Does anyone know how to achieve this in Qlik?

PowerBI ExampleQlik Example

[TimvB]

This is not possible in Qlik, as fields with the same name from different tables will always form keys. Therefore, you should rename the fields and possibly redesign the data model.

Below are some links to resources that are helpful when designing a data model in Qlik:

Data modeling best practices 

Loading multiple fact data structures 

Hope it helps!

[Rodj · Accepted solution]

Hi @greend21 ,

As @TimvB has said, the answer to your basic question is that you can't. Qlik uses an entirely different type of logic in what's called the Associative engine (aka: QIX) to how any other SQL or set based logic works. The associative approach overcomes many of the issues that typically occur with set based logic and has a great many advantages, however it does at times demand a slightly different way of modelling your data. Once you come to grips with that, something that's not all that difficult to do, you'll find it a much more flexible way to work with data.

The references Tim provided are great, reviewing the data modelling best practices is a good starting point for understanding what the associative engine is doing. With respect to your particular challenge you'll want to focus on either building a link table or look at concatenating your two main tables. If you search the web for Qlik Link table for example you'll find many examples/tutorials on how it is done to overcome this kind of issue. A quick search I did just now came up this one that seems appropriate to you.

Cheers,

Rod

[petter]

It doesn't seem that you have any circular reference in your data model. Having a synthetic key which you have in your data model might not be a problem at all - despite there being a misconception that all synthetic keys are bad both on the community and amongst developers. 

Taking a step back and looking closer at the data model from Power BI it seems like you have two fact tables and four dimension tables.

1. The two fact tables seem to share all the dimensions
2. The most common recommendation from Qlik experts is that concatenating (appending) the two fact tables is most often the best thing to do.
3. The none-shared fields in the resulting concatenated table will have null values for the opposite original fact table which works very well. They will share common dimensions without any problem - which is also very well.
4. You will have to use Set Analysis (which is more correctly referred to as Set Expression) to avoid mixing the two fact tables in certain expressions/measures. Like this:
   
   Sum(  { <MasterType={'AR'}>} <shared-fieldname> )
    
     or
   
   Sum(  { <MasterType={'CE'}>} <shared-fieldname> )

Concatenation can be done like this:

AR_CE_Master:
LOAD 'AR' AS MasterType , *  ;   SQL SELECT * FROM AR_Master;
CONCATENATE LOAD 'CE' AS MasterType , * ;   SQL SELECT * FROM CE_Master;

// continue loading the four dimension tables as they are 
// and you will avoid any synthetic key and AVOID having to rename any fieldnames

[Mvp]

Hi TimvB, 

The two resources you have listed above do not appear to be available any longer. Do you know of an up-to-date reference. 

Regards,

M.