How to Join a Sales Person and Year Key with Fact Table Indirectly via Multiple Tables?

[jannis_qlik]

Hello,

I’m working on a QlikSense project where I need to join a table containing forecast data (FORECAST) based on a key composed of [Year] and [Sales Person]. However, the challenge is that these fields are not directly available in a single table within my data model.

Here’s the structure of my data:

1. FACTS: Contains KeyDate (from which I extract Year) and KeyDocument, which links to HEADINFORMATION.
2. HEADINFORMATION: Contains KeyDocument and CustomerID, which links toCUSTOMER.
3. CUSTOMER: Contains CustomerID and Sales Person.

To clarify:

• I need to join theFORECASTtable, which uses a composite key of Year and Sales Person, to the fact data.
• However, Year is only available in the FACTS table, and Sales Person is only in the DEBITOR table.

What is the best approach to create a reliable data model that enables me to link FORECAST with the fact data using this composite key, given that the necessary fields are spread across different tables? I tried joining them by creating a composite key using temporary tables, but I’m not sure if this is the most efficient or correct method.

Any advice or best practices would be greatly appreciated!

[Vegar · Accepted solution]

The most obvious answer is that you will need to either get [Sales person] on to your fact table; or join customer and header info on to your forecast so you get the forecast on [KeyDocument] (not Sales Person) and [Year] level.