Linking fact table to dimension table 2 ways

[MartinW1]

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?

[Chanty4u]

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;

[MartinW1]

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?

[HirisH_V7]

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);

[Vegar]

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)

[MartinW1]

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

[MartinW1]

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.

[MartinW1 · Accepted solution]

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

[Nagaraju_KCS]

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!

[MartinW1]

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.