From arrays to data pipelines

What you will build

First, a five-month revenue model with a cumulative series and bear/base/bull scenarios. Then you will inspect a fulfillment model to learn when an Array should give way to a List, Map, Set, or Deque.

1. Map two arrays together

Download the starter above, then complete the first formula from the canonical Array Analytics example:

A1 = [120, 135, 142, 150, 168]
A2 = [42, 44, 47, 49, 52]
A3 = MAP(A1, A2, (units, price) => ROUND(units * price, 2))

Checkpoint: A3 = [5040, 5940, 6674, 7350, 8736].

MAP preserves the five-element shape while the lambda combines corresponding unit and price values.

2. Keep a running result and one terminal result

A4 = SCAN(0, A3, (acc, value) => ROUND(acc + value, 2))
A5 = REDUCE(0, A3, (acc, value) => ROUND(acc + value, 2))
A6 = ROUND(A5 / 5, 2)

Checkpoint: A4 = [5040, 10980, 17654, 25004, 33740], A5 = 33,740, and A6 = 6,748.

Use SCAN when every intermediate accumulator matters. Use REDUCE when only the final accumulator matters.

3. Build a shaped scenario matrix

G1 = [0.92; 1.00; 1.08]
G5 = [INDEX(A3, 1, month) * INDEX(G1, scenario, 1) FOR scenario IN 1..3, month IN 1..5]
M5 = BYROW(G5, row => SUM(row))
G9 = BYCOL(G5, col => AVERAGE(col))

The three rows are bear, base, and bull. The five columns are months.

Checkpoint: M5 = [31040.8; 33740; 36439.2]. Because the scenario multipliers average to 1, G9 = [5040, 5940, 6674, 7350, 8736].

The compact outputs settle at A8 = 33,740, A9 = "bull-led", and A10 = "total=33740 avg=6748 scenario=bull-led".

4. Choose a collection by the operation

Open the canonical Collection Pipelines example. It uses four native persistent collections:

Updates return new revisions; they do not mutate the prior value.

Checkpoint: A5 = 5 while revised A4 has length A6 = 6; B4 = 92, B5 = 0, B6 = 4, and B7 = FALSE; C6 = TRUE, C7 = FALSE, C8 = 7, and C9 = TRUE; D4 = "j-0987", D5 = "j-1044", D12 = 3, and D13 = FALSE.

5. Publish to an array, then pipe

F1 = VECTOR_TO_ARRAY(A4)
F2 = F1 |> FILTER(quantity => quantity < 10) |> MAP(quantity => (10 - quantity) * 6.25) |> SUM()

VECTOR_TO_ARRAY makes the native List explicit at the workbook boundary. The terminal pipeline keeps filter, transformation, and aggregation together.

Checkpoint: F1 = [6, 12, 7, 25, 3, 9] and F2 = 93.75. The delivery-log fold also produces F6 = 55 units of ink.

Try it yourself

In Array Analytics, change the last units value from 168 to 180.

Checkpoint: the last monthly revenue is 9,360; A5 and A8 become 34,364; A6 = 6,872.8; and M5 = [31614.88; 34364; 37113.12].

In Collection Pipelines, change VECTOR_SET(A1, 1, 6) to VECTOR_SET(A1, 1, 8).

Checkpoint: F1 = [8, 12, 7, 25, 3, 9] and F2 = 81.25. The original A1 still has five entries and its fourth value remains 25.

You are done when

• The paired array, cumulative series, and scenario totals match the checkpoints.
• You can explain the shape preserved or produced by MAP, SCAN, BYROW, and BYCOL.
• You can choose Array, List, Map, Set, or Deque from the operation you need.
• A collection revision leaves its predecessor unchanged.