Associate multiple data tables by more than 1 link

[JoshPark]

Hi guys, 

I'm not really the technical guy and I'm trying to associate multiple data tables with more than 1 link on Qlik Sense Cloud. 

Here are my tables. 

- 'Actual Sales' and 'Cost of Goods Sold' both are linked with 'Product' by 'Item Code'. 
- 'Target Sales' is linked with 'Product' by 'Product Model'. 

However, when I make the combo chart to show Actual Sales and Target Sales, with dimension as YearMonth, I would have 3 different choices for YearMonth(based on 'Actual Date', 'COGS Date', 'Target Date')
so the chart can only display Actual Sales (if I choose YearMonth generated from Actual Sales Date) and Target Sales is fixed value. 

Then I come back to 'Data Manager' mode to try associate these tables with date field, but encounter circular references. 
I find this article:
https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/LoadData/understand-circ...

I try to add in 'Data Load Editor' mode the following: 
"LOAD
Actual Date, COGS Date, Target Date 
as
Date"
But it doesn't work, now Actual Sales and Target Sales are all fixed value. 

Besides, my data tables have more different fields with each other so that when I try to concate these tables as one, there are lots of null value and the data seems really invalid.

Can anyone guide me how to fix this issue or do you have any suggestion? 

Thank you!

[Lemac]

You are trying to match dates, and products and are creating maybe circular references. 

What you could try to do here is make a different key. You could probably make a key which is: 'Product_Month_Year'. That new field: 'Product_Month_Year', should be added to Actual Sales and Target Sales. For Actual Sales it would be: [Actual Date.autocalendar.YearMonth]&'_'&[Product Model] , and for Target Sales [Target Date.autocalendar.YearMonth]&'_'&[Product Model] (or both on [Item Code], depending on which is more generic)

Then you can join these tables directly to each other. 

[MayilVahanan]

Hi 

Try to create the master calendar for those dates with help of bridge table.

Canonical Date - Qlik Community - 1463578

[JoshPark]

HI @Lemac ,

Thank you for your suggestion. 

I try to generate a key field in each table in Data Manager like this

However, on "Actual Sales" table I only have "Item Code", while on "Target Sales" table I only have "Product Model". 
As the data I have, 1 Product Model contains various Item Code, so it cannot really matching to map. 

Do you have any idea?

[Lemac]

How I have solved this is by using a different Transformer-app. 

I would fix this in the back-end by the data load editor. But there are other ways to solve this:

Target_Sales: //First, we make the Product Month for Target Sales
LOAD
*
FROM [lib://@[your source]/TARGET_SALES.QVD]
(qvd);

Target_Sales_2:
NoConcatenate Load
*,
[Product Model]&month(floor(Target_Date)) as Product_Month
Resident Target_Sales;

Actual_Sales: //Second, we make the Product Month for Actual Sales
LOAD
*
FROM [lib://@[your source]/ACTUAL_SALES.QVD]
(qvd);


Left join (Actual_Sales)

LOAD
[Item Code],
[Product Model]
FROM [lib://@[your source]/PRODUCT.QVD]
(qvd);

Actual_Sales_2:
NoConcatenate Load
*,
[Product Model]&month(floor(Actual_Date)) as Product_Month

Resident Actual_Sales;
Drop Table Actual_Sales;

outer join (Target_Sales) //Then we join them to make one table.
Load
*
Resident Actual_Sales_2;

drop Table Actual_Sales_2;