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

    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
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. 1 Sebastien Labonne June 9, 2010 at 6:02 pm


    Thanks for the tip.

    A formula solution might look like this



  2. 2 Roy MacLean 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

June 2010
« May   Jul »

%d bloggers like this: