topic Re: Linking fact table to dimension table 2 ways in App Development

Linking fact table to dimension table 2 ways

MartinW1 — Thu, 06 Nov 2025 17:58:46 GMT

I am relatively new to the Qlik universe and am setting a data model up in Qlik Sense, but really struggling to get it linking correctly, without circular references.

The tables/fields in question are:

Sales - ProductID, SalesSource, CustomerID, SalesValue

Product - ProductID, CategoryLevel1, CategoryLevel2

Targets - SalesSource, CustomerID, CategoryLevel1, CategoryLevel2, SalesTarget

The problem is with linking both Sales and Targets to Product via different fields when the 2 are linked themselves (or in a concatenated fact table, as I have it at the moment). I've tried various versions of link tables and synthetic keys, but can't get it working correctly.

Any suggestions/pointers please?

Re: Linking fact table to dimension table 2 ways

Chanty4u — Thu, 06 Nov 2025 21:01:01 GMT

You can use link table

Just search on link table implementation

// Load distinct combinations of the linking fields from both fact tables

LinkTable:

LOAD DISTINCT

SalesSource,

CustomerID,

CategoryLevel1,

CategoryLevel2

RESIDENT Targets;

CONCATENATE (LinkTable)

LOAD DISTINCT

SalesSource,

CustomerID,

CategoryLevel1,

CategoryLevel2

RESIDENT Sales

INNER JOIN (Sales)

LOAD

ProductID,

CategoryLevel1,

CategoryLevel2

RESIDENT Product;

Re: Linking fact table to dimension table 2 ways

MartinW1 — Thu, 06 Nov 2025 23:21:05 GMT

Thank you, but I’m not following how this helps - Sales will still need to be linked directly to Product via ProductID, I think? And this link table will also be linked to Product via the category levels, so won’t I still have circular references?

Re: Linking fact table to dimension table 2 ways

HirisH_V7 — Fri, 07 Nov 2025 06:31:28 GMT

Hi check out this, Before applying the ApplyMap function, ensure that the first two mapping tables contain distinct records — that is, no duplicate keys exist in either mapping table.

ProductMap: MAPPING LOAD ProductID, CategoryLevel1, CategoryLevel2 FROM [Product.qvd] (qvd); TargetMap: MAPPING LOAD SalesSource & '|' & CustomerID & '|' & CategoryLevel1 & '|' & CategoryLevel2 AS TargetKey, SalesTarget FROM [Targets.qvd] (qvd); Sales: LOAD *, ApplyMap( 'TargetMap', SalesSource & '|' & CustomerID & '|' & CategoryLevel1 & '|' & CategoryLevel2, 0 ) AS SalesTarget; LOAD ProductID, SalesSource, CustomerID, SalesValue, ApplyMap('ProductMap', ProductID, 'Unknown') AS CategoryLevel1, ApplyMap('ProductMap', ProductID, 'Unknown') AS CategoryLevel2 FROM [Sales.qvd] (qvd);

Re: Linking fact table to dimension table 2 ways

Vegar — Fri, 07 Nov 2025 07:35:47 GMT

A question to ask yourself or your stakeholder: what target should be displayed if you selects a single product that share categories with other products?

Should it display the total target value for the product category and source combination? Or should you display nothing, because you don't have a targer on that detail level?

For the later I would consider to concatenate the sales and target tables into the same fact table.

For the first I would consider joining in the product categories on to the sales table, and then use the combination of the two categories and SalesSource be the associated link between the sales and target tables. (Here I assume that the ProductID is a unique key for the product table to avoid the join causing a fan out duplication)

Re: Linking fact table to dimension table 2 ways

MartinW1 — Fri, 07 Nov 2025 13:13:11 GMT

Yes, when any dimension is selected that doesn't have targets, 0 should be displayed. I'm handling that in the target measures atm. And I already have a version of the model with sales and targets in one fact table, but I'm struggling to get the link(s) to products working in either version

Re: Linking fact table to dimension table 2 ways

MartinW1 — Fri, 07 Nov 2025 12:47:51 GMT

I'll give this a go, but looking at it quickly, won't this only show a sales target for lines where there are sales? I will need it to show 0 sales but with a target value in that instance.

Re: Linking fact table to dimension table 2 ways

MartinW1 — Fri, 07 Nov 2025 17:14:33 GMT

As an update to this, I found a solution using a combination of the suggestions above.

[Product]: LOAD ProductID, CategoryLevel1, CategoryLevel2; Concatenate([Product]) LOAD DISTINCT 'TargetDummy' AS ProductID, CategoryLevel1, CategoryLevel2; [ProductMapL1]: MAPPING LOAD ProductID, CategoryLevel1 RESIDENT Product; [ProductMapL2]: MAPPING LOAD ProductID, CategoryLevel2 RESIDENT Product; [Sales]: LOAD ProductID, SalesSource, CustomerID, ApplyMap('ProductMapL1', ProductID, 'Unknown') AS CategoryLevel1, ApplyMap('ProductMapL2', ProductID, 'Unknown') AS CategoryLevel2, Sales; [Targets]: LOAD 'TargetDummy' AS ProductID, SalesSource, CustomerID, CategoryLevel1, CategoryLevel2, Target;

You can then create a key or link table (or just let Qlik create a synthetic key) with ProductID, CategoryLevel1 & CategoryLevel2. It's probably not the "best" way round it, but it's working

Re: Linking fact table to dimension table 2 ways

Nagaraju_KCS — Mon, 10 Nov 2025 09:36:12 GMT

Try this

Highlighted different colors for table association

Sales:
LOAD
ProductID, // Key to Product
SalesSource&CustomerID as Key, // Key to Targets (part of composite key)
SalesValue
FROM Sales;

Product:
LOAD
ProductID, // Key to Sales
CategoryLevel1,
CategoryLevel2
FROM Products;

Targets:
LOAD
SalesSource&CustomerID as Key,
CategoryLevel1,
CategoryLevel2,
SalesTarget
FROM Target;

Hope this helps!

Re: Linking fact table to dimension table 2 ways

MartinW1 — Mon, 10 Nov 2025 09:50:20 GMT

This is how I had it to begin with. Using this method there is either:

- no link between Targets and Product, so targets do not show against Category levels 1 or 2

- a link to Product via the 2 Category levels, creating a circular reference

This was my original issue I was trying to solve.

Re: Linking fact table to dimension table 2 ways

Nagaraju_KCS — Mon, 10 Nov 2025 10:20:07 GMT

yeah, got it - you can try this as well

Sales:

LOAD

ProductID,

SalesSource,

CustomerID,

SalesValue

from Sales;

left join

Product:

LOAD

ProductID,

CategoryLevel1,

CategoryLevel2

From Product;

Sales_Product:

Load ProductID,

SalesSource,

CustomerID,

CategoryLevel1,

CategoryLevel2,

SalesValue as Sales,

'Value' as Flag

Resident Sales;

Drop table Sales;

Concatenate

Targets:

LOAD 'Dummy' as ProductID,

SalesSource,

CustomerID,

CategoryLevel1,

CategoryLevel2,

SalesTarget as Sales,

'Target' as Flag

From Target;