Data Modelling | Three table synthetic key

[aslamshaikh]

Hello Team,

I always feel stuck in preparing data model , today I have three tables mentioned below along with some column names

Table A - InstanceId , Col B, Col C 

Table B - InstanceId , UserID , Col D , Col DD

Table C - InstanceId , UserID , Col E ,  Col EE

When I load this data then a synthetic key is created between table B and C. To avoid this I had created composite key in table B and C ( InstanceID & UserID) but then also there is a syn key present. One relationship with InstanceId in all three table and another relationship with composite key with Table B and C.

I have to create three sheets for three table and I want to use a common filter of UserId across the sheet.

Your help will be much appreciated , please guide me how can I solve this issue and also please let me know how can I improve my confidence in data modelling.

Thanks and Regards

Aslam Shaikh

[panosalexand · Accepted solution]

Hi Aslam,

Try to load the tables with the method of Qualify and Unqualify. That means all the fields will get the table prefix at the beginning. Let me explain:

//---------------------------------------------
QUALIFY *;

UNQUALIFY  InstanceId;

Table A:
Load
InstanceId , Col B, Col C ;

UNQUALIFY *;

//------------------------------------------------

QUALIFY *;

UNQUALIFY  key, InstanceId;

Table B:
Load
InstanceId , UserID , Col D , Col DD,

InstanceId&UserID as key;

UNQUALIFY *;

Table B -

//---------------------------------------------
QUALIFY *;

UNQUALIFY key, InstanceId  ;

Table C:
Load
InstanceId , UserID , Col E ,  Col EE,

InstanceId&UserID as key;

UNQUALIFY *;

//------------------------------------------------

Let me know

[marcus_sommer · Accepted solution]

The recommended data-model in Qlik is a star-scheme with a single fact-table and n surrounding dimension-tables. In this regard don't try to keep fact-tables separated else merge them vertically (concatenate) and/or horizontally (join or mapping).