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.