Link table

[Ernests]

Hi Experts,

I'm facing an issue with my Qlik data model and would appreciate your advice.

Currently, I have Plan and Actual tables linked by three fields:

• Source

• Position

• Month

The Source field represents the company department.

The challenge is that, in the Plan table, some records don't contain the original department. Instead, they contain a "Summary" value. This is expected and correct according to the business logic and data model.

However, I would like to use Source as a filter. At the moment, if a user selects an original department (for example, Post Office) from the Actual table, the corresponding Plan records are not displayed because they are linked to Summary instead of the selected department.

Ideally, when a user selects Source = Post Office, both the corresponding Actual data and the related Plan data should be returned.

Any ideas?

Thanks in advance!

kopa = Plan

gramatojumi = Actual

Avots = Source

[Chanty4u]

Hi 

Use a Link Table with a Mapping Table

Create a mapping between the detailed departments and the summary value.

Example:

Department

PlanSource

Post Office

Summary

Finance

Summary

HR

Summary

Then create a Link Table like:

Source

PlanSource

Position

Month

Post Office

Summary

Manager

Jan

Link:

Actual → Source

Plan → PlanSource

Shared fields → Position, Month

This allows a selection of Post Office to associate with the Summary plan records.

[NAM_climber]

This is a classic Qlik associative engine challenge. The engine is behaving correctly  Source = "Post Office" genuinely does not match Source = "Summary"  but the business logic requires it to. You have a few ways to solve this depending on how much you want to change the data model versus the expressions.

Option 1: Expand Summary records in the load script (recommended if data volume allows)

In the load script, for every Plan record where Source = 'Summary', duplicate it once per actual department. This means the engine sees a "Post Office" row in Plan for every Summary record, so selections work naturally with no changes to expressions or front-end logic.

// Step 1: Load distinct departments from Actual
TempDepts:
LOAD DISTINCT Source AS Dept
FROM Actual;

// Step 2: Load non-Summary Plan records normally
Plan:
LOAD Source, Position, Month, Amount
FROM Plan
WHERE Source <> 'Summary';

// Step 3: Load Summary Plan records and cross-join with departments
TempSummary:
LOAD Position, Month, Amount
FROM Plan
WHERE Source = 'Summary';

// Cross join - no common field between TempDepts and TempSummary
// so Qlik produces a Cartesian product
CONCATENATE(Plan)
LOAD
    Dept AS Source,
    Position,
    Month,
    Amount
RESIDENT TempDepts
JOIN (TempDepts) LOAD * RESIDENT TempSummary;

DROP TABLES TempDepts, TempSummary;

The trade-off is data volume: if you have 10 departments and 10,000 Summary rows, you end up with 100,000 expanded rows. Usually fine, but worth being aware of.

Option 2: Island filter table with Set Analysis (cleanest model, no data expansion)

Create a disconnected Source filter table that has no key to either Plan or Actual. Users select from this island dimension, and all measures use set analysis to reference it explicitly.

In the script:

// Island filter - disconnected from both tables
Filter_Source:
LOAD DISTINCT Source AS [Filter Source]
FROM Actual;

Then in your chart expressions:

// Actual measure
Sum({<Source = [Filter Source] >} Actual_Amount)

// Plan measure - always includes Summary regardless of filter
Sum({<Source = {"Summary"} + [Filter Source] >} Plan_Amount)

The user selects from the "Filter Source" field, which drives both expressions via set analysis rather than the associative engine. This keeps your data model clean and is the most scalable approach, but it means every measure needs to be written with this pattern, and you lose the native click-to-filter feel unless you build it carefully.

Option 3: Minimal change, force Summary into Plan measure with set analysis

If changing the data model is not an option right now, you can at minimum stop Plan from being filtered by Source at all:

// Plan measure ignores Source selection entirely
Sum({<Source =>} Plan_Amount)

This returns all Plan data regardless of the Source selection, which is not ideal but may be acceptable if the Position and Month filters are doing most of the meaningful filtering anyway.

Which to choose

If your Plan table is not enormous, Option 1 is the most user-friendly because the associative engine just works and you do not need to rewrite expressions. Option 2 is the most architecturally correct for larger datasets or more complex models. Option 3 is a short-term workaround but loses filtering integrity on Plan.

The underlying principle is the same in all three cases: you need to bridge the semantic gap between "Summary" and the actual department names somewhere, and that somewhere is either the script, the expressions, or both.