Key Findings

Weight Range Strategy

After analyzing the reference output, I discovered that the weight range suffixes (SUB1, 1LB, 6LB, 10LB) do NOT mean “filter to only this weight range”. Instead, they indicate WHICH SERVICE LEVEL KEY to use as the PRIMARY data source, but ALL sheets should contain complete weight ranges.

Data Combination Pattern

When a mapping has multiple service keys like:

['SUB 1LB 2025', 'ECONOMY 2025', 'GROUND RESIDENTIAL']

The system creates 4 sheets:

  1. SUB1 sheet:

    • Starts at 1 oz
    • Uses SUB 1LB data for weights < 1 lb (< 16 oz)
    • Falls back to ECONOMY for weights >= 1 lb
    • Falls back to GROUND RESIDENTIAL for higher weights
    • Contains FULL weight range (1-150 oz)

  2. 1LB sheet:

    • Starts at 1 lb (16 oz)
    • Uses ECONOMY as primary source
    • Falls back to GROUND RESIDENTIAL for higher weights
    • Contains weights from 16-150 oz

  3. 6LB sheet:

    • Starts at 6 lbs (96 oz)
    • Uses GROUND RESIDENTIAL as primary source
    • Contains weights from 96-150 oz

  4. 10LB sheet:

    • Starts at 10 lbs (160 oz)
    • Uses GROUND RESIDENTIAL as primary source
    • Contains weights from 160-150 oz (or max)

Current Implementation Issues

  1. Only using first matching service level - Should merge data from ALL matched service levels
  2. Filtering by weight - Should NOT filter, but use appropriate service level based on weight
  3. Missing combined data - Each sheet needs data from multiple service levels combined

Correct Algorithm

For each mapping with multiple service keys:

  1. Match ALL service level keys to source data
  2. Create 4 sheets (if SUB 1LB key exists, otherwise 1 sheet)
  3. For each sheet:
    • Determine starting weight based on sheet type (SUB1=1oz, 1LB=16oz, 6LB=96oz, 10LB=160oz)
    • For each weight row:
      • Check which service level has data for this weight
      • Use the most specific/appropriate service level (SUB 1LB < ECONOMY < GROUND)
      • Combine rates from appropriate source
  4. Result: Each sheet has complete, continuous weight range using best available data source per weight

Example

For UPS GROUND with keys ['RESIDENTIAL GROUND 2025', 'COMMERCIAL GROUND 2025']:

  • Should match BOTH RESIDENTIAL GROUND and COMMERCIAL GROUND service levels
  • Create separate sheets for each (or choose one based on logic)
  • Reference shows “UPS - Ground Residential” and “UPS - Ground Commercial” as separate entries

This suggests that when multiple non-SUB1LB keys exist, we should create SEPARATE sheets for each key, not combine them.

Revised Strategy

The actual pattern is:

  • If mapping has “SUB 1LB” key: Create 4 sheets combining SUB1LB + ECONOMY + GROUND data
  • If mapping has only standard keys: Create 1 sheet per unique service level found
  • Each sheet uses appropriate data for its weight range but extends as far as possible

Status

Current v2 implementation creates 40 sheets vs reference’s 59 sheets (19 missing).

Main gaps:

  1. Not creating all 4 weight variant sheets for SUB1LB mappings (only creating 2)
  2. Not handling COMMERCIAL GROUND vs RESIDENTIAL GROUND as separate outputs
  3. Not properly combining data from multiple service levels
  4. Customer name should be “The Absorption Company” not source value