### Sub-sequence Iterator 3

In the previous post, we were looking at an iterator object that bound to successive sub-sequences of records in a table, according to a StartCondition and an EndCondition. In that example, the sub-sequences were contiguous, non-overlapping, and so could be summarized to give sub-totals in a very flexible way.

A slightly different example is where the start and end records for each sub-sequence are all intermixed. Here’s a time-ordered sequence of start and finish actions, for some identified entities:

You can see that B and C both start and finish before A finishes.

Suppose that want to find out the duration of each activity. We then need to find matching start and finish records, and calculate the difference between the two dates. In this case, the sub-sequence is the ‘lifetime’ of the entity. Although we are not interested in anything other than the start and finish for an entity, it’s conceivable in other examples that we might be interested in the intermediate values.

In the previous example, the sub-sequences were non-overlapping, so the MoveNext operation started looking for a new StartRow immediately after the old EndRow. Here, the sub-sequences can overlap, so the MoveNext operation starts looking for a new StartRow immediately after the old StartRow . At present, each entity is assumed to have a finish record.

StartCondition simply looks for a ‘start’ record; when it finds one, it records the Id. EndCondition looks for a ‘finish’ record with the current Id.

The client code creates an iterator on the Actions table (see above), moves it through the table, calculates the duration for each entity (finish – start + 1), and writes a summary record:

Here’s the client procedure:

```Sub Run()
Dim ssi As SSI2
Set ssi = New SSI2
With ssi
.WsName = "Actions"    'Worksheet
.TableName = "ActionTable"
.KeyName1 = "Id"       'Key column
.KeyName2 = "Action"   'there are 2 key columns
.ValName = "Date"      'Value column
End With

Worksheets("Summary2").Activate  'for the output
ActiveSheet.Cells.Clear

Dim row As Long
row = 1
ssi.MoveNext     'to first sub-sequence
Do Until ssi.IsAfter
Range("A" & row).Value = ssi.Label
Range("B" & row).Value = Summarized(ssi.values)
row = row + 1
ssi.MoveNext
Loop
End Sub

Private Function Summarized(values As Variant) As Variant
'pre: IsArray(values)
Dim last As Long
last = UBound(values)
Summarized = values(last) - values(1) + 1
End Function```

I think this is quite an interesting example, as it’s not obvious to me how you would do it with formulas, even array ones.

At present there are two different classes for the two examples (non-overlapping and overlapping) – hence SSI2, above. Maybe they should both implement a common interface. In practice, though I think you’d know which flavour you needed. Here’s the workbook with both examples, if you want to try it out.

#### 2 Responses to “Sub-sequence Iterator 3”

1. June 9, 2010 at 6:02 pm

Roy,

Thanks for the tip.

A formula solution might look like this

=SUMPRODUCT(–(A1:A11=”A”),–(B1:B11=”Finish”),(C1:C11))-SUMPRODUCT(–(A1:A11=”A”),–(B1:B11=”Start”),(C1:C11))+1

Sébastien

2. June 10, 2010 at 8:43 am

Thanks, Sébastien,
However, your formula is for a single specified entity. So you would need to know (and if necessary change) the set of Ids and the values of those Ids – the ‘=”A”‘ in your formula. Whereas the scenario I had in mind is that the table is being frequently extended with new records, so the set of entities grows over time.

What I like about the iterator solution is that (with a little coding) it is generic both as to the sub-sequences that are detected, and the Summarized function that operates on the subsequence values.