Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
Hi.
I get the idea of the solution.
Thank you much for help. 😊