I have 2 fact tables which hold data related by an ID field where it's available in both tables.
Each table has a datetime field which if the ID exists in both tables can differ by a few minutes to a few hours between. It's possible to have several records per ID in both tables with different datetime stamps.
The ID is not necessarily in both tables. It can just be in Fact1 or just be in Fact2.
The ID field is repeated in both tables after a period of time (usually around 10 weeks, but can be fewer or more weeks). This pattern repeats, so over a period of a year, it's possible that the data will repeat 5-6 times.
My challenge is to try and match the pairs of data, based on the ID, and both dates being within 24 hours of each other.
The datasets are large (approx. 2.5m rows for 3 months in each table).
Can anyone suggest a way that I can join the data using a date range in the key? I've tried Interval Match (creating a [Fact2 End Date] by adding 1 day on to [Fact1 Date] :
NoConcatenate
Fact1Match:
Load *
Resident Fact1;
Fact2Match:
Load *
Resident Fact2;
IntervalMatch:
IntervalMatch ([Fact2 Date])
Load distinct [Fact1 Date], [Fact1 End Date]
Resident Fact1;
This works where there is a match, but what I need is something that also works where we only have data in one table of the other.
I also tried concatenating the two fact tables into one, but this caused other issues, and I ended up with multiple rows even where the ID matched one record on each fact table.
Can anyone help please?
Many thanks.:)
