Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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];
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];