Interval match to check award level

[yipchunyu]

I have a sale fact table group by individual sales level like:

 sales, case count, sales amount

We are going to award the sales based on either the case count or the sales amount

So, I build a requirement table, sth like below

Level	Award	Requirement	AmtMin	AmtMax
0	Not Qualifed	case	(9,999,999.00)	528,300.00
1	Member	case	528,300.00	1,584,900.00
2	Court of the Table	case	1,584,900.00	3,169,800.00
3	Top of the Table	case	3,169,800.00	9,999,999,999.00
0	Not Qualifed	Amount	(9,999,999.00)	2,113,200.00
1	Member	Amount	2,113,200.00	6,339,600.00
2	Court of the Table	Amount	6,339,600.00	12,679,200.00
3	Top of the Table	Amount	12,679,200.00	9,999,999,999.00

CASE_REQ:
LOAD
[AmtMin] AS [CaseAmtMin],
[AmtMax] AS [CaseAmtMax],
[Level] AS [CaseLevel]
RESIDENT REQ
where Requirement ='Case'
;

SALES_REQ:
LOAD
[AmtMin] AS [SalesAmtMin],
[AmtMax] AS [SalesAmtMax],
[Level] AS [SalesLevel]
RESIDENT REQ
where Requirement ='Sales'
;

INNER JOIN IntervalMatch ( [# Total CASE] )
LOAD
[CaseAmtMin],
[CaseAmtMax]
Resident Case_REQ;

INNER JOIN IntervalMatch ( [# Total Sales] )
LOAD
[SalesAmtMin],
[SalesAmtMax]
Resident Sales_REQ;

It works but I just wondering whether it's the correct way to go.

Any other better solutions?  

[yipchunyu · Accepted solution]

Find a pattern which can solve most of the issue as sth like below:

// Link Fields from raw with [# Total ] into the SALES_REQ ========
INNER JOIN INTERVALMATCH ([# Total])
LOAD
[AmtMin],
[AmtMax]
Resident SALES_REQ;

JOIN (raw) LOAD * RESIDENT SALES_REQ;
DROP TABLE SALES_REQ;
DROP FIELD [AmtMin], [AmtMax];