Qlik Sense data model - keys

[AnnaC]

Hi

I have 3 dimension tables: 

1. Product (Prod_Id, Category, etc.)

2. Customer (Customer_Id, Account, Customer_name, etc.)

3. Calendar (Date, etc.)

I have also 4 fact tables:

1. Sales (Date, Prod_Id, Account, etc.)

2. Targets (Date, Category, Customer_name, etc.)

3. Search_data1 (Date, Category, etc.)

4. Search_data2( Date, Prod_ID, etc.)

The Date field in every table should link to the Calendar table.

The Account field in the Sales table should link to the Customer table.

The Prod_Id field in the Sales table and the Search_data2 table should link to the Product table.

The Category in Search_data1 and Targets should link to the Product table.

The Customer_name field in Targets should link to the Customer table.

I am struggling to create a data model with these tables. Can anyone point me into the right direction?

Thanks!

[mato32188]

Hi Anna,

what if you would have only one FACT table created from your 4 tables?

FACT:

Load *, 'Sales' as TableType FROM XXX;

concatenate(FACT)

Load *, 'Targets' as TableType FROM XXX;

concatenate(FACT)

Load *, 'Search1' as TableType FROM XXX;

concatenate(FACT)

Load *, 'Search2' as TableType FROM XXX;

Then load the rest of dimensions (Product, Customer, Calendar).

I would suggest you to use Exists in your where clause for each dimension to load only relevant key values.

Load * FROM Product where Exists(Prod_Id); etc.

BR

Martin

[AnnaC]

Hi Martin,

Thank you for your reply. If I do that it looks as if they are all linked in the Data Model Viewer. But then when I try to sum sales from the Sales table by category, it doesn't link the Prod_Id field to the Product table to find the category.

Kind regards

Anna