Postage calculation
A certain B2C website needs to calculate the shipping cost of an order. In most cases, the shipping cost is determined by the total weight of the package. However, when the price of the order exceeds $300, free shipping is provided. The detailed rules are shown in the mailCharge table below:
This table records the postage for each field within various value ranges. For example, the first record indicates that when the COST field value is between 300 and 1000000, the postage is 0 (free shipping); The second record indicates that when the value of the WEIGHT field is between 0 and 1 (kg), the postage is 10 (USD).
Here are some testOrders from the website:
Please calculate the detailed postage for these orders.
Find the records with COST and WEIGHT field values in the mailCharge records separately, and then loop through the entire order records. First, check whether the COST value in the order record meets the free standard. If it does not meet the standard, determine the postage level based on the weight.
| A | B | |
|---|---|---|
| 1 | =T("mailCharge.txt ") | =T("testOrder.txt") |
| 2 | =A1.select@1(FIELD=="COST") | =A1.select(FIELD=="WEIGHT").sort(MINVAL) |
| 3 | =B1.derive(if(A2.MINVAL < COST, A2.CHARGE, B2.segp@r(MINVAL, WEIGHT).CHARGE):POSTAGE) |
https://try.esproc.com/splx?4Rn
A1 reads the mailCharge table, B1 reads the order data.
A2 selects free standard data, and since this is a single record, adds the @1 option; B2 Selects the weight billing standard and sort it in ascending order by weight range:
A3 adds a postage field to the order data, and when setting it, first determine whether the discount amount has been reached based on COST, and set the corresponding discount postage (free in this example); Otherwise, use SEGP to find the interval where WEIGHT is located in the postage standard and obtain the corresponding postage to complete the setting. Since the interval used is a left open and right closed interval, the @r option is added. The final result is as follows:
