Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to do an interval match with two set of intervals? In my fact data, I have the field month end, which looks in the interval Effective Start and Effective Stop. In that same interval match function, can I have the Sales field from my fact data, look in the interval Min Threshold and Max Threshold as well? This is ultimately so I can find out what scale to give my fact data, based on if the sales is between a certain amount and if the sales took place between a certain date range.
Here is what my two tables look like.
Fact Data:
Sales | MonthEnd | Employee |
$40 | 3/30/3024 | A |
$25 | 1/31/2024 | B |
$81 | 1/31/2024 | A |
$78 | 1/31/2024 | C |
$110 | 4/30/2024 | A |
$2 | 4/30/2024 | A |
$179 | 3/31/2024 | B |
$63 | 2/29/2024 | C |
$92 | 2/29/2024 | A |
Dimension/Intervals Data:
Effective Start | Effective Stop | Min Threshold | Max Threshold | Scale |
1/1/2024 | 2/29/2024 | 46 | 100,000,000 | Pass |
1/1/2024 | 2/29/2024 | 0 | 45 | Fail |
3/1/2024 | 3/30/2024 | 51 | 100,000,000 | Pass |
3/1/2024 | 3/30/2024 | 0 | 50 | Fail |
4/1/2024 | 12/31/9000 | 0 | 130 | Fail |
4/1/2024 | 12/31/9000 | 131 | 100,000,000 | Pass |
Hello
I found a way to do what you need, via the following steps:
The final model looked like this:
Dim_Employee:
Load * Inline [
Employee
A
B
C
];
Fact:
Load * Inline [
Sales|MonthEnd|Employee
40|3/30/3024|A
25|1/31/2024|B
81|1/31/2024|A
78|1/31/2024|C
110|4/30/2024|A
2|4/30/2024|A
179|3/31/2024|B
63|2/29/2024|C
92|2/29/2024|A
] (delimiter is |);
Intervals:
Load * Inline [
Effective Start|Effective Stop|Min Threshold|Max Threshold|Scale
1/1/2024|2/29/2024|46|100000000|Pass
1/1/2024|2/29/2024|0|45|Fail
3/1/2024|3/31/2024|51|100000000|Pass
3/1/2024|3/31/2024|0|50|Fail
4/1/2024|12/31/9000|0|130|Fail
4/1/2024|12/31/9000|131|100000000|Pass
] (delimiter is |);
Left Join(Intervals)
Load
Employee
Resident Dim_Employee;
Drop Table Dim_Employee;
Inner Join IntervalMatch ( MonthEnd, Employee )
LOAD [Effective Start], [Effective Stop], [Employee]
Resident Intervals;
Inner Join IntervalMatch ( Sales, Employee, MonthEnd )
LOAD [Min Threshold],[Max Threshold], [Employee], [MonthEnd]
Resident Intervals;
Drop Table Fact;
Table1:
NoConcatenate
Load Distinct
[Effective Start],
[Effective Stop],
[Min Threshold],
[Max Threshold],
[Scale],
[Employee],
[MonthEnd],
[Sales]
Resident Intervals;
Drop Table Intervals;
Hello
I found a way to do what you need, via the following steps:
The final model looked like this:
Dim_Employee:
Load * Inline [
Employee
A
B
C
];
Fact:
Load * Inline [
Sales|MonthEnd|Employee
40|3/30/3024|A
25|1/31/2024|B
81|1/31/2024|A
78|1/31/2024|C
110|4/30/2024|A
2|4/30/2024|A
179|3/31/2024|B
63|2/29/2024|C
92|2/29/2024|A
] (delimiter is |);
Intervals:
Load * Inline [
Effective Start|Effective Stop|Min Threshold|Max Threshold|Scale
1/1/2024|2/29/2024|46|100000000|Pass
1/1/2024|2/29/2024|0|45|Fail
3/1/2024|3/31/2024|51|100000000|Pass
3/1/2024|3/31/2024|0|50|Fail
4/1/2024|12/31/9000|0|130|Fail
4/1/2024|12/31/9000|131|100000000|Pass
] (delimiter is |);
Left Join(Intervals)
Load
Employee
Resident Dim_Employee;
Drop Table Dim_Employee;
Inner Join IntervalMatch ( MonthEnd, Employee )
LOAD [Effective Start], [Effective Stop], [Employee]
Resident Intervals;
Inner Join IntervalMatch ( Sales, Employee, MonthEnd )
LOAD [Min Threshold],[Max Threshold], [Employee], [MonthEnd]
Resident Intervals;
Drop Table Fact;
Table1:
NoConcatenate
Load Distinct
[Effective Start],
[Effective Stop],
[Min Threshold],
[Max Threshold],
[Scale],
[Employee],
[MonthEnd],
[Sales]
Resident Intervals;
Drop Table Intervals;
Brilliant and I love the logic! It worked like a charm, thank you so much @rodrigo_martins !