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.

About these ads