Hi All,
I have a table as below. Here I need to check if the units fall under this threshold and check the discount type to apply the discount by percentage or by value. How can I achieve this?
Customer | Product | Units | Net Value | Threshold1 | Threshold2 | Threshold3 | Threshold4 | Threshold5 | Discount Type | Discount Amt1 | Discount Amt2 | Discount Amt3 | Discount Amt4 | Discount Amt5 | Discount %1 | Discount %2 | Discount %3 | Discount %4 | Discount %5 |
Cust1 | P1 | 14 | 1000 | 5 | 10 | 15 | 20 | | Amount | 25 | 50 | 75 | 100 | 125 | | | | | |
Cust1 | P2 | 20 | 2000 | 10 | 20 | 30 | 40 | 50 | Percentage | | | | | | 5% | 10% | 15% | 20% | 25% |
Cust2 | P3 | 120 | 3000 | 25 | 50 | 100 | | | Amount | 50 | 100 | 150 | | | | | | | |
Cust3 | P4 | 30 | 4000 | 10 | 20 | 30 | 40 | | Percentage | | | | | | 10% | 20% | 30% | 40% | |
Cust4 | P5 | 40 | 5000 | 20 | 40 | 60 | 80 | | Amount | 100 | 200 | 300 | 400 | 500 | | | | | |
conditions should be as below.
- Check Discount type (Amount or Percentage)
- Then units should be verified with threshold –
- T1 =< Units < T2 then discount Amt or % 1
- T2 =< Units < T3 then discount Amt or % 2
- T3 =< Units < T4 then discount Amt or % 3
- T3 =< Units < T5 then discount Amt or % 4
- T4 =< Units then discount Amt or % 5
We need to handle the Threshold null as well (ex: Cust2 & P3 where Units is 120 but max threshold is T3 (100) & T4 is null
so below will be the discount.
Customer | Product | Units | Net Value | Discount |
Cust1 | P1 | 14 | 1000 | 50 |
Cust1 | P2 | 20 | 2000 | 200 |
Cust2 | P3 | 120 | 3000 | 150 |
Cust3 | P4 | 30 | 4000 | 1200 |
Cust4 | P5 | 40 | 5000 | 200 |
@sunny_talwar Any suggestions on this?