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

jannis_qlik — Mon, 02 Sep 2024 08:14:07 GMT

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:

FACTS: Contains KeyDate (from which I extract Year) and KeyDocument, which links to HEADINFORMATION.
HEADINFORMATION: Contains KeyDocument and CustomerID, which links toCUSTOMER.
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!

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

Vegar — Mon, 02 Sep 2024 15:20:07 GMT

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.

topic Re: How to Join a Sales Person and Year Key with Fact Table Indirectly via Multiple Tables? in App Development

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

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