How to reuse calculations across related tables

This guide uses vector functions to summarize table health, audit delivery history, measure supplier performance from partial receipts, and re-rank suppliers after each order assignment. Call the same calculations with different datasets or updated inputs without copying their intermediate steps into each script.

For a calculation applied independently to each row, use a pure function instead. For a running calculation that maintains state across ordered rows, use a process function.

Summarize a table’s health

Use a vector function to collect the same structural checks and descriptive statistics for different input tables. This example takes four explicit columns: a candidate text key, a number, a date, and a text label. It does not automatically discover columns or their types; choose the columns at the call site, or extend the function’s arguments for additional columns.

Keep the candidate key as an ordinary column until after the check. Declaring it as the table’s primary dimension would reject duplicate keys before the function could report them.

def vector analyzeTable<<H, T>>(
  T.Key : text, T.Value : number, T.When : date, T.Label : text) with
  expect table H[report]
  expect table T[row]

  H.Rows = count(T.*) into Scalar
  H.DuplicateRows = H.Rows - (distinct(T.Key) into Scalar)
  H.UniqueKey = H.DuplicateRows == 0
  H.EmptyKeys = count(T.Key == "") into Scalar
  H.MinValue = min(T.Value) into Scalar
  H.MaxValue = max(T.Value) into Scalar
  H.AvgValue = avg(T.Value) into Scalar
  H.MinDate = min(T.When) into Scalar
  H.MaxDate = max(T.When) into Scalar
  H.AvgDate = date(2001, 1, 1) + round(avg(T.When - date(2001, 1, 1)) into Scalar)
  H.EmptyLabels = count(T.Label == "") into Scalar
  return (H.Rows, H.UniqueKey, H.DuplicateRows, H.EmptyKeys,
    H.MinValue, H.MaxValue, H.AvgValue, H.MinDate, H.MaxDate, H.AvgDate, H.EmptyLabels)

table Deliveries = with
  [| as Ref, as Qty, as Received, as Label |]
  [| "D1", 10, date(2024, 9, 1), "First receipt" |]
  [| "D2", 20, date(2024, 9, 3), "" |]
  [| "D2", 30, date(2024, 9, 5), "Repeated reference" |]
  [| "", 40, date(2024, 9, 7), "" |]

table Report = extend.range(1)
Report.Rows, Report.UniqueKey, Report.DuplicateRows, Report.EmptyKeys,
    Report.MinValue, Report.MaxValue, Report.AvgValue,
    Report.MinDate, Report.MaxDate, Report.AvgDate, Report.EmptyLabels =
    analyzeTable<<Report, Deliveries>>(
      Deliveries.Ref, Deliveries.Qty, Deliveries.Received, Deliveries.Label)

hasRows = single(Report.Rows) > 0

show summary "Table health" a1f12 with
  single(Report.Rows) as "Rows"
  single(Report.UniqueKey) as "Ref: unique"
  single(Report.DuplicateRows) as "Ref: duplicate rows"
  single(Report.EmptyKeys) as "Ref: empty"
  if hasRows then text(single(Report.MinValue)) else "N/A" as "Qty: minimum"
  if hasRows then text(single(Report.MaxValue)) else "N/A" as "Qty: maximum"
  if hasRows then text(single(Report.AvgValue)) else "N/A" as "Qty: average"
  if hasRows then text(single(Report.MinDate)) else "N/A" as "Received: earliest"
  if hasRows then text(single(Report.MaxDate)) else "N/A" as "Received: latest"
  if hasRows then text(single(Report.AvgDate)) else "N/A" as "Received: average"
  single(Report.EmptyLabels) as "Label: empty"

The summary reports:

Report is a one-row output table, bound to H; Deliveries is the input table, bound to T. The into Scalar expressions aggregate over the whole input table. The function returns typed statistics into Report, while the caller chooses how to display them.

DuplicateRows counts occurrences beyond the first occurrence of each key: two rows with "D2" contribute one duplicate, not two. Uniqueness does not assert that the table contains rows or that its keys are non-empty; check the row and empty-key counts separately. Empty-text counts use == "", so whitespace-only values are not counted as empty.

The average date is calculated from day offsets relative to January 1, 2001, then rounded to a whole day and converted back to a date. It is an average calendar date, not an average lead time. For an empty input table, the display shows “N/A” for numeric and date statistics instead of presenting the aggregators’ default values as observations.

Audit delivery data

Identify the tables and inputs

Give the function two table parameters: S for suppliers and D for deliveries. Pass the order date, receipt date, and quantity from D, plus the audit date and maximum age in days for each supplier in S.

For this example, use completed deliveries with strictly positive quantities; returns and cancellations belong in a separate dataset. Apply these rules in order:

1. No delivery records: “No history”.
2. A non-positive quantity, a receipt before its order, or a receipt after the audit date: “Invalid rows”.
3. The latest valid receipt is older than the chosen threshold: “Stale history”.
4. Otherwise: “OK”.

“No history” and “Stale history” are review flags, not proof of bad data: a supplier may simply have had no recent activity. “OK” means these checks passed, not that every possible data-quality issue has been ruled out.

Define and call the function

Establish the relationship between Deliveries and Suppliers before calling the function. Here, expect Deliveries.supplier = Deliveries.Supplier requires each delivery to reference a known supplier. Unmatched references must be resolved before this audit can run.

The following script is self-contained:

def vector deliveryDataHealth<<S, D>>(
  D.OrderDate : date, D.ReceiptDate : date, D.Quantity : number,
  S.AsOf : date, S.MaxAge : number) with
  expect table S[id]
  expect table D expect [id]

  D.Invalid = D.Quantity <= 0 or D.ReceiptDate < D.OrderDate or D.ReceiptDate > S.AsOf
  S.Rows = count(D.*)
  S.InvalidRows = count(D.*) when (D.Invalid)
  S.LastReceipt = max(D.ReceiptDate) when (not D.Invalid)
  S.Status = if S.Rows == 0 then "No history" else
      if S.InvalidRows > 0 then "Invalid rows" else
      if S.LastReceipt < S.AsOf - S.MaxAge then "Stale history" else "OK"
  return S.Status

table Suppliers[supplier] = with
  [| as supplier, as MaxAge |]
  [| "S1", 30 |]
  [| "S2", 30 |]
  [| "S3", 60 |]
  [| "S4", 30 |]

table Deliveries = with
  [| as Supplier, as Ordered, as Received, as Qty |]
  [| "S1", date(2024, 9, 1), date(2024, 9, 8), 10 |]
  [| "S1", date(2024, 9, 20), date(2024, 9, 24), 8 |]
  [| "S2", date(2024, 9, 25), date(2024, 9, 21), 5 |]
  [| "S2", date(2024, 9, 20), date(2024, 9, 25), 10 |]
  [| "S3", date(2024, 6, 1), date(2024, 6, 10), 4 |]

expect Deliveries.supplier = Deliveries.Supplier

Suppliers.Health = deliveryDataHealth<<Suppliers, Deliveries>>(
  Deliveries.Ordered, Deliveries.Received, Deliveries.Qty,
  date(2024, 9, 30) into Suppliers, Suppliers.MaxAge)

show table "Delivery data health" a1d5 with
  supplier
  Suppliers.Health
  order by supplier

The resulting table is:

S2 has a recent valid receipt, but its other receipt predates the order. The invalid-row check takes precedence over freshness. S3 has only an old receipt, and S4 has no records.

Suppliers takes the place of S, and Deliveries takes the place of D. The local dimension name id refers to the caller’s supplier dimension. Column arguments bind by position. date(2024, 9, 30) into Suppliers supplies the same audit date for every supplier, while Suppliers.MaxAge allows a different freshness threshold per supplier.

Only the returned status is assigned to Suppliers.Health. Intermediate checks and aggregates stay inside the function.

Measure supplier performance from partial receipts

To calculate an on-time, in-full (OTIF) rate, first determine when each order was fully received, then compare that date with the promised date and aggregate the results per supplier. Put these dependent steps in one function so reporting scripts use the same definition.

Use one row per single-product order, with positive ordered and received quantities in the same unit. Resolve returns, corrections, and cancellations beforehand. In this example, each order has equal weight. Only orders due on or before the assessment date enter the rate; overdue incomplete orders count as failures. Keep orders with no receipts in the input, and ignore receipts after the assessment date.

def vector supplierPerformance<<S, O, R>>(
  O.Quantity : number, O.Promised : date, O.AsOf : date,
  R.Order : text, R.Received : date, R.Quantity : number) with
  expect table S[supplier]
  expect table O[orderId] expect [supplier]
  expect table R expect [orderId]

  R.ObservedQty = if R.Received <= O.AsOf then R.Quantity else 0
  R.Cumulative = sum(R.ObservedQty) by R.Order scan R.Received
  O.Complete = sum(R.ObservedQty) >= O.Quantity
  O.CompletedOn = min(R.Received) when (R.Cumulative >= O.Quantity)
  O.OnTime = O.Complete and O.CompletedOn <= O.Promised
  S.DueOrders = count(O.*) when (O.Promised <= O.AsOf)
  S.OnTimeOrders = count(O.*) when (O.Promised <= O.AsOf and O.OnTime)
  S.OTIF = S.OnTimeOrders / max(1, S.DueOrders)
  return (S.DueOrders, S.OTIF)

table Suppliers[supplier] = with
  [| as supplier |]
  [| "S1" |]
  [| "S2" |]
  [| "S3" |]
  [| "S4" |]

table Orders[orderId] = with
  [| as orderId, as Supplier, as Qty, as Promised |]
  [| "PO1", "S1", 10, date(2024, 9, 10) |]
  [| "PO2", "S1", 5, date(2024, 9, 12) |]
  [| "PO3", "S2", 8, date(2024, 9, 15) |]
  [| "PO4", "S2", 3, date(2024, 10, 2) |]
  [| "PO5", "S3", 6, date(2024, 9, 20) |]

table Receipts = with
  [| as Order, as Received, as Qty |]
  [| "PO1", date(2024, 9, 8), 4 |]
  [| "PO1", date(2024, 9, 10), 6 |]
  [| "PO1", date(2024, 9, 14), 1 |]
  [| "PO2", date(2024, 9, 13), 5 |]
  [| "PO3", date(2024, 9, 12), 3 |]
  [| "PO3", date(2024, 9, 15), 5 |]
  [| "PO4", date(2024, 10, 1), 3 |]
  [| "PO5", date(2024, 9, 21), 2 |]

expect Orders.supplier = Orders.Supplier
expect Receipts.orderId = Receipts.Order

Suppliers.DueOrders, Suppliers.OTIF = supplierPerformance<<Suppliers, Orders, Receipts>>(
  Orders.Qty, Orders.Promised, date(2024, 9, 30) into Orders,
  Receipts.Order, Receipts.Received, Receipts.Qty)

show table "Supplier delivery performance" a1d5 with
  supplier
  Suppliers.DueOrders
  if Suppliers.DueOrders == 0 then "N/A" else "\{100 * Suppliers.OTIF}%" as "OTIF"
  order by supplier

The resulting table is:

PO1 reaches its ordered quantity on September 10. The extra receipt on September 14 does not make it late: use the first date the cumulative quantity reaches the target, not the date of the last receipt. PO2 is late, PO3 is on time, PO4 is not yet due, and PO5 is overdue but incomplete. S4 has no due orders, so its rate is displayed as “N/A”, not as a delivery failure.

The function combines a receipt-level scan, order-level completion checks, and supplier-level aggregations. The caller only establishes the table relationships and supplies the inputs. Call it with another assessment date or another dataset to reuse the whole calculation; no intermediate columns need to be recreated outside the function.

Re-rank suppliers after each order

When assigning orders among several suppliers, a supplier’s estimated lead time may increase with the number of orders already assigned to it. The fastest supplier for one order may no longer be the fastest for the next. Put the lead-time calculation and ranking in a vector function, then call it again after each assignment with the updated order counts.

Assume four equal-sized orders for the same item, with all suppliers eligible. For this example, each supplier has a base lead time and an extra number of days per previously assigned order. No orders complete during this planning sequence, and later assignments do not revise earlier orders’ quoted lead times. This is an illustrative model, not a general supplier lead-time rule. The selection uses lead time only; costs and other sourcing constraints are outside the example. Unique supplier codes break lead-time ties.

def vector rankSuppliers<<S>>(
  S.Code : text, S.BaseDays : number, S.ExtraDays : number,
  S.Assigned : number) with
  expect table S[id]
  S.LeadDays = S.BaseDays + S.ExtraDays * S.Assigned
  S.Rank = rank() scan [S.LeadDays, S.Code]
  return (S.LeadDays, S.Rank)

table Suppliers = with
  [| as Code, as BaseDays, as ExtraDays |]
  [| "A", 5, 3 |]
  [| "B", 7, 3 |]
  [| "C", 9, 2 |]

table Orders = extend.range(4)
Orders.Supplier = ""
Orders.LeadDays = 0
Suppliers.Assigned = 0

loop step in 1..4
  Suppliers.LeadDays, Suppliers.Rank = rankSuppliers<<Suppliers>>(
    Suppliers.Code, Suppliers.BaseDays, Suppliers.ExtraDays, Suppliers.Assigned)
  chosen = single(Suppliers.Code) when (Suppliers.Rank == 1)
  leadDays = single(Suppliers.LeadDays) when (Suppliers.Rank == 1)
  Orders.Supplier = if Orders.N == step then chosen else Orders.Supplier
  Orders.LeadDays = if Orders.N == step then leadDays else Orders.LeadDays
  Suppliers.Assigned = Suppliers.Assigned + (if Suppliers.Code == chosen then 1 else 0)

show table "Supplier choices" a1d5 with
  Orders.N as "Order"
  Orders.Supplier
  Orders.LeadDays as "Lead time (days)"
  order by Orders.N

The resulting sequence is:

After the first order goes to A, its next lead-time estimate rises from 5 to 8 days, so B wins the second order at 7 days. B’s estimate then rises to 10 days, making A the fastest again for the third order. Assigning that order raises A’s estimate to 11 days, so C wins the fourth order at 9 days.

The vector function recalculates the lead times and global ranks from Suppliers.Assigned; the loop records the next choice and increments only that supplier’s count. Suppliers remain eligible for later orders. A pure function could compute each supplier’s lead time independently, but would leave the comparison and ranking outside the function. Calling the vector function only once before the loop would leave the ranks unchanged as orders accumulate. This four-order example illustrates reuse within a bounded loop, not a general optimizer for an arbitrary number of orders.

Reuse it in another script

Move a function definition into a module and change def vector to export def vector. In the other script, import the module and call the function through its alias, supplying that script’s tables and columns. Keep the read and show statements in the calling script.

For the complete module/import syntax, see exporting a vector function. For another dataset, establish the corresponding table relationships and pass the tables and columns in the same order. Adapt the inputs without duplicating the calculation logic.