Creating a table with 2 date dimensions.

[peachman]

Hi,

I have 2 Main Tables Loaded into my Qlik App

Table 1: 

ID	START_DATE	END_DATE	Tab1_Value
1	11/10/2021	12/10/2021	10
2	11/10/2021	13/10/2021	10
3	11/10/2021	14/10/2021	10
4	12/10/2021	14/10/2021	10

Table 2 (Targets type file): 

Weekending (Fridays)	Tab2_Value	Cumulative
15/10/2021	100	5000

I want to Link these 2 tables in table within the App itself to have an output of the following in a Pivot table. The SUM(Tab1_Value) is based off WEEKEND(END_DATE,0,5). 

Weekending	SUM(Tab1_VALUE)	Tab2_Value
15/10/2021	40	100
..

I have been able to create a simple table using the Table2 as this is a very straight table, however I need to aggregate Table 1 to link with the weekending dimension? Picture attached of my current output. As you can see all the Tab1_Values are missing. 

I have tried to use an AGGR() Function - however this yields blank? is there anyway to get table 1 and table 2 to appear on the same table in qlik based on the weekending as described above?? 

[chrismarlow]

Hi,

I think you need to force association within the script, so maybe something similar to this;

[table 1]:
Load * inline [
ID,	START_DATE,	END_DATE,	Tab1_Value
1,	11/10/2021,	12/10/2021,	10
2,	11/10/2021,	13/10/2021,	10
3,	11/10/2021,	14/10/2021,	10
4,	12/10/2021,	14/10/2021,	10
];

[Table 2]:
load *,
	[Weekending (Fridays)] AS [END_DATE];
load * inline [
Weekending (Fridays),	Tab2_Value,	Cumulative
15/10/2021,	100,	5000
];

Then you can use END_DATE in a calculated dimension;

Cheers,

Chris.