Are the values to be bucketted in a single field in the source data? If so you will want to create the rebate field during load, like this;
num(if(Value < 0, 0,
if(Value <= 10000000, 0.005,
if(Value <= 30000000, 0.01,
if(Value <= 100000000, 0.015,
if(Value <= 250000000, 0.02,
0.03))))), '#,##0.0%') as [Rebate Pct],
You can then do a Preceeding Load to get the actual rebate, like this;
Value * [Rebate Pct] as Rebate
If you need to aggregate to get to the value that you need to calculate the rebate on, then you probably want to use a GROUP BY in the load script, from which you can then calculate the rebate value. That's a touch more involved, but just a number of small steps to get to the right result.
Hope that helps,
So, do you need to take into account all transactions to date to decide which rebate bucket a customer is on, or is it within a certain time frame?
If it is for all time you could do it in the load script, using a RESIDENT load. This loads from a table which has already been loaded and allows you to do further manipulation.
So, if you had a CustomerID and a number of Values you needed to total up, it would be:
sum(Value) as [Customer Value To Date],
num(if(sum(Value) < 0, 0,
if(sum(Value) <= 10000000, 0.005,
if(sum(Value) <= 30000000, 0.01,
if(sum(Value) <= 100000000, 0.015,
if(sum(Value) <= 250000000, 0.02,
0.03))))), '#,##0.0%') as [Rebate Pct]
GROUP BY CustomerID
In the front end of the application you could then get the total value of Rebates as:
Or the total value with rebates removed as:
Hope that helps?