Creating Key Table for Tables that have only part of the mutual fields relative to one another

[Jenny]

Hi,

I'm struggling with creating a model for tables that have Part of the mutual field relative to one another.

For Example I have 3 different tables with these fields:

Table1, fields:

A,

B,

C,

D,

E

Table2, fields:

A,

B,

F

Table3, fields:

A,

C,

H

In SQL for example if I would want to join Tbl2 and Tbl3 to Tbl1 I would left join tbl2 on fields A and B and left join tbl3 on fields on A and C to generate 1 big table having all the columns.

But I cant figure out how to do the same in Model in QlikSense. I need to be able to use all of the columns later on in the Dashboard Creation step.

How Can I create a correct connection between the 3 tables using KeyTable? Or maybe something else?

Thank you 🙂

[pallavi_96]

Hi @Jenny ,

You can try creating a composite key using two columns to join the tables. For example:

- To join Table1 and Table2, create a key by combining columns A and B (e.g., A&B as Key1) in both tables.

- Similarly, to join Table1 and Table3, create a key using A and C (e.g., A&C as Key2) in both tables.

[marcus_sommer]

Joins in Qlik aren't completely equally to those in sql but the essential logic is the same and therefore what you are describing should be working in Qlik, too.

t: load * from t1; left join(t) load * from t2; left join(t) load * from t3;

[Jenny]

But I dont want to join them straightforward Like I would do in SQL because my real tables are really big so I dont want to create 1 huge single table.

Is there a way using KeyTable here?

[marcus_sommer · Accepted solution]

In general an appropriate link-table containing the distinct (combined) keys of all associated tables be created. But it won't be a benefit - neither from the needed efforts and complexity nor in regard to the performance - much more likely would be a big disadvantage.

The officially recommended data-model is a star-scheme with a single fact-table and n dimension-tables. And the bigger the data-sets are the more important is this recommendation. 

[Jenny]

Thank you.