Forum Discussion
Helper IMatch Closest Length to Existing Available Length Between Two Tables
So I have two tables. One with new sales orders and one with existing inventory. I want to match the existing inventory to the sales orders by both material # and the closest length that is longer than the requested length. I can't match a sales order to shorter existing inventory but anything that is equal to or longer is acceptable. Below would be sample examples of tables and what I would like to match in yellow between the batches and the lengths. In the example, you can see that some orders would not have matching inventory available, but others would potentially have multiple available lengths that would work, but I want the closest to (equal or longer) to match to the ordered length by best fit. Therefore, if you had an order at 3 length, and on hand inventory at 3.5 and 4, I would want 3.5 to match to the order not 4.
| Table 1 | ||
| Sales Order # | Material # | Length Ordered |
| 1 | A | 3 |
| 2 | A | 3.5 |
| 3 | B | 9 |
| 4 | C | 4 |
| 5 | B | 6.5 |
| 6 | A | 4.25 |
| 7 | C | 2 |
Thanks in advance for your help!
| Table 2 | ||
| Material # | Batch | On Hand Length |
| A | 11 | 4.35 |
| A | 22 | 2.95 |
| A | 33 | 4.15 |
| B | 44 | 8.5 |
| B | 55 | 6.75 |
| C | 66 | 4.25 |
| C | 77 | 2.25 |
| Matched Table | ||||
| Sales Order # | Material | Length Ordered | Matched Batch | On Hand Length |
| 1 | A | 3 | No Match | No Match |
| 2 | A | 3.5 | 33 | 4.15 |
| 3 | B | 9 | No Match | No Match |
| 4 | C | 4 | 66 | 4.25 |
| 5 | B | 6.5 | 55 | 6.75 |
| 6 | A | 4.25 | 11 | 4.35 |
| 7 | C | 2 | 77 | 2.25 |
Try these calculated columns in Table1:
Matched Batch = VAR vMaterial = Table1[Material #] VAR vLengthOrdered = Table1[Length Ordered] VAR vInventory = FILTER ( Table2, Table2[Material #] = vMaterial && Table2[On Hand Length] >= vLengthOrdered ) VAR vMinOnHandLength = MINX ( vInventory, Table2[On Hand Length] ) VAR vMinBatch = MINX ( FILTER ( vInventory, Table2[On Hand Length] = vMinOnHandLength ), Table2[Batch] ) VAR vResult = IF ( ISBLANK ( vMinBatch ), "No Match", CONVERT ( vMinBatch, STRING ) ) RETURN vResultOn Hand Length = VAR vMaterial = Table1[Material #] VAR vLengthOrdered = Table1[Length Ordered] VAR vInventory = FILTER ( Table2, Table2[Material #] = vMaterial && Table2[On Hand Length] >= vLengthOrdered ) VAR vMinOnHandLength = MINX ( vInventory, Table2[On Hand Length] ) VAR vResult = IF ( ISBLANK ( vMinOnHandLength ), "No Match", CONVERT ( vMinOnHandLength, STRING ) ) RETURN vResultAwesome! Worked like a Champ! Thanks so much!
2 Replies
- DataInsights
Super User
Try these calculated columns in Table1:
Matched Batch = VAR vMaterial = Table1[Material #] VAR vLengthOrdered = Table1[Length Ordered] VAR vInventory = FILTER ( Table2, Table2[Material #] = vMaterial && Table2[On Hand Length] >= vLengthOrdered ) VAR vMinOnHandLength = MINX ( vInventory, Table2[On Hand Length] ) VAR vMinBatch = MINX ( FILTER ( vInventory, Table2[On Hand Length] = vMinOnHandLength ), Table2[Batch] ) VAR vResult = IF ( ISBLANK ( vMinBatch ), "No Match", CONVERT ( vMinBatch, STRING ) ) RETURN vResultOn Hand Length = VAR vMaterial = Table1[Material #] VAR vLengthOrdered = Table1[Length Ordered] VAR vInventory = FILTER ( Table2, Table2[Material #] = vMaterial && Table2[On Hand Length] >= vLengthOrdered ) VAR vMinOnHandLength = MINX ( vInventory, Table2[On Hand Length] ) VAR vResult = IF ( ISBLANK ( vMinOnHandLength ), "No Match", CONVERT ( vMinOnHandLength, STRING ) ) RETURN vResult 
heiligbdAwesome! Worked like a Champ! Thanks so much!