Master Fact table creation

Peony — Sat, 16 Nov 2024 20:09:44 GMT

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

Re: Master Fact table creation

Vegar — Sat, 31 Aug 2019 17:06:05 GMT

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

Re: Master Fact table creation

Peony — Mon, 02 Sep 2019 06:49:15 GMT

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

topic Master Fact table creation in QlikView

Master Fact table creation

Re: Master Fact table creation

Re: Master Fact table creation