Master Fact table creation

[Peony]

Hi All.

I have two fact table that looks like these:

Users	Plan Date	Plan Amount
User 1	2019-Jan	100
User 1	2019-Feb	200
User 1	2019-Mar	300
User 1	2019-Apr	400
User 2	2019-Jan	150
User 2	2019-Feb	250
User 2	2019-Mar	350
User 2	2019-Apr	450

Users	Fact Date	Fact Amount
User 1	01/01/2019	123
User 1	02/01/2019	80
User 1	08/01/2019	88
User 1	09/01/2019	123
User 1	13/01/2019	456
User 1	13/01/2019	47
User 1	14/01/2019	77
User 1	15/01/2019	7
User 2	01/03/2019	114
User 2	14/03/2019	59
User 2	14/03/2019	448
User 2	15/03/2019	1658
User 2	15/03/2019	1
User 2	15/03/2019	15
User 2	15/03/2019	12
User 2	23/03/2019	148
User 2	24/03/2019	36
User 2	25/03/2019	369
User 2	26/03/2019	85
User 2	27/03/2019	588
User 2	28/03/2019	1
User 2	29/03/2019	1

And I need to join them into one Fact table, that should looks like below. Please, could you help to understood how I may to connect them in a proper way?

Users	Plan Date	Fact Date		Fact Amount
User 1	2019-Jan	01/01/2019	100	123
User 1	2019-Jan	02/01/2019	100	80
User 1	2019-Jan	08/01/2019	100	88
User 1	2019-Jan	09/01/2019	100	123
User 1	2019-Jan	13/01/2019	100	456
User 1	2019-Jan	13/01/2019	100	47
User 1	2019-Jan	14/01/2019	100	77
User 1	2019-Jan	15/01/2019	100	7
User 1	2019-Feb		200
User 1	2019-Mar		300
User 1	2019-Apr		400
User 2	2019-Jan		150
User 2	2019-Feb		250
User 2	2019-Mar	01/03/2019	350	114
User 2	2019-Mar	14/03/2019	350	59
User 2	2019-Mar	14/03/2019	350	448
User 2	2019-Mar	15/03/2019	350	1658
User 2	2019-Mar	15/03/2019	350	1
User 2	2019-Mar	15/03/2019	350	15
User 2	2019-Mar	15/03/2019	350	12
User 2	2019-Mar	23/03/2019	350	148
User 2	2019-Mar	24/03/2019	350	36
User 2	2019-Mar	25/03/2019	350	369
User 2	2019-Mar	26/03/2019	350	85
User 2	2019-Mar	27/03/2019	350	588
User 2	2019-Mar	28/03/2019	350	1
User 2	2019-Mar	29/03/2019	350	1
User 2	2019-Apr		450

[Vegar · Accepted solution]

Try this:

LOAD 
   Users, 
   [Plan Date],
   num(Date#([Plan Date], 'YYYY-MMM')) as %key, 
   Date#([Plan Date], 'YYYY-MMM') as [Plan Amount]
FROM
  FACTDATA;
JOIN
LOAD 
   Users, 
   [Fact Date],
   num(Monthstart(date#([Fact Date],'DD/MM/YYYY')) )as  %key, 
   Fact Amount]
FROM
   PLANDATA
;
DROP FIELD %key;

See attached qvw

[Peony]

Hi.
I get the idea of the solution.
Thank you much for help. 😊