Interval Match with two intervals to match between

[MassicotPSCU]

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

[rodrigo_martins · Accepted solution]

Hello
I found a way to do what you need, via the following steps:

• I generated the Employee field in the Intervals table, I did this through a join with an inline of just one field (Employee). This was necessary to use Interval Match with a key field, as with just the MonthEnd field it was not possible to identify which Employee the resulting row belonged to after the join.
• I did the first Interval Match with the MonthEnd field and the Employee key
• Then, I did the second Interval Match, with the Sales field and the MonthEnd and Employee keys
• The result still gave me duplicate lines that I resolved by using distinct at the end.

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;

[MassicotPSCU]

Brilliant and I love the logic! It worked like a charm, thank you so much @rodrigo_martins !