generate table using 3 existing tables - relation many many

[Charles_Tex]

Hello everyone,

I'm having a hard time trying to create a table using 3 existing tables with a many many relationship.

The 3 tables I have are:

• a date table

date	day of week	day of month	month
01/11/2021	1	1	11
02/11/2021	2	2	11
03/11/2021	3	3	11
04/11/2021	4	4	11
05/11/2021	5	5	11
06/11/2021	6	6	11
07/11/2021	7	7	11
08/11/2021	1	8	11
09/11/2021	2	9	11
10/11/2021	3	10	11
11/11/2021	4	11	11
12/11/2021	5	12	11

• a foreacast table (at a month granularity)

Shop	Category	Month	forecast
SHOP-1	bedroom	nov.-21	100 000 €
SHOP-1	bedroom	dec.-21	80 000 €
SHOP-1	kitchen	nov.-21	90 000 €
SHOP-1	kitchen	dec.-21	50 000 €
SHOP-2	bedroom	nov.-21	60 000 €
SHOP-2	bedroom	dec.-21	50 000 €
SHOP-2	kitchen	nov.-21	20 000 €
SHOP-2	kitchen	dec.-21	8 000 €

• a % of increase based on the day of week.

Shop	Category	day of week	% increase
SHOP-1	bedroom	1	10%
SHOP-1	bedroom	2	20%
SHOP-1	bedroom	3	5%
SHOP-1	bedroom	4	2%
SHOP-1	bedroom	5	12%
SHOP-2	bedroom	1	7%
SHOP-2	bedroom	2	6%
SHOP-2	bedroom	3	5%
SHOP-2	bedroom	4	12%
SHOP-2	bedroom	5	45%
SHOP-1	Kitchen	1	7%
SHOP-1	Kitchen	2	20%
SHOP-1	Kitchen	3	5%
SHOP-1	Kitchen	4	2%
SHOP-1	Kitchen	5	12%
SHOP-2	Kitchen	1	7%
SHOP-2	Kitchen	2	6%
SHOP-2	Kitchen	3	5%
SHOP-2	Kitchen	4	12%
SHOP-2	Kitchen	5	45%

this is my data model:

What i'm trying to acheive is a table with the forecast and % of increase for each attributs shops/Category and date. Something that looks like this:

date	day of week	month	shop	category	% of increase	Forecast
01/11/2021	1	11	SHOP-1	bedroom	10%	100 000
02/11/2021	2	11	SHOP-1	bedroom	20%	100 000
03/11/2021	3	11	SHOP-1	bedroom	5%	100 000
04/11/2021	4	11	SHOP-1	bedroom	2%	100 000
05/11/2021	5	11	SHOP-1	bedroom	12%	100 000
06/11/2021	6	11	SHOP-1	bedroom	Null	100 000
07/11/2021	7	11	SHOP-1	bedroom	Null	100 000
08/11/2021	1	11	SHOP-1	bedroom	10%	100 000
09/11/2021	2	11	SHOP-1	bedroom	20%	100 000
10/11/2021	3	11	SHOP-1	bedroom	5%	100 000
11/11/2021	4	11	SHOP-1	bedroom	2%	100 000
12/11/2021	5	11	SHOP-1	bedroom	12%	100 000
01/11/2021	1	11	SHOP-2	Kitchen	7%	20 000
02/11/2021	2	11	SHOP-2	Kitchen	6%	20 000
03/11/2021	3	11	SHOP-2	Kitchen	5%	20 000
04/11/2021	4	11	SHOP-2	Kitchen	12%	20 000
05/11/2021	5	11	SHOP-2	Kitchen	45%	20 000
06/11/2021	6	11	SHOP-2	Kitchen	Null	20 000
07/11/2021	7	11	SHOP-2	Kitchen	Null	20 000
08/11/2021	1	11	SHOP-2	Kitchen	7%	20 000

I've tried by using GENERATE / ADDCOLUMNS / GENERATESERIES / SELECTEDCOLUMNS but unfornately i havent found the correct formula...

Any help would be greatly appreciated,

thx,

Charles

[BeaBF]

@Charles_Tex Hi!

Can you paste the data of the 3 tables and not like images? 

[Charles_Tex]

Hello BF,

I've updated my previous post with the tables.

Thx for your help