Grouping and Referencing Data Using Calculate By Set Stream

Scenario

For a given account, you want to find the difference between each consecutive debit/credit to the account.

Example

You want to look back at a previous record within a group in a stream, or create a cumulative total per group. You get the same number of records as you put in.

A calculate by set stream is useful if you need to group data, but also reference the values on individual records within the group.

You will need to group your input pipe and set your stream type to 'CalculateBySet'.

The attribute expressions for your key fields will be _key[n], where n is the order number of your key field grouping.

If you want to refer to a value against the individual record, then you should prefix it with _current., e.g. _current.StartDate.

If you want to refer to the group, then you should prefix your attribute expression with _this. For example, max(_this.IssueID) would return the largest IssueID in the current group.

If you want to refer to all records in the stream set, then you should prefix your attribute expression with the pipe name. For example, min(in.StartDate) would return the earliest StartDate in the stream set.

Unlike all other stream types, records in a stream set are processed one by one, in the order specified. This can be useful, because you can number records in the order that they are processed and refer to previously processed records

The internal variable '_itemNumber' contains the current number of the record in the group.

It is possible to reference previous records' values by using this item number. For example, _this[_ItemNumber-1].Description, would return the description from the previous record.

Please note that it may make more sense to use a sequence, rather than _itemNumber in some scenarios, where you want to number records.

Calculate by set streams are often used when you want to create a cumulative value across the whole stream. This is done in conjunction with the cache function.

Further Details on Internal Variables For ‘Calculate By Set’ Streams

_current and _this are only used in ‘Calculate By Set’ streams.

_current. refers to the record that is being processed.
_this. refers to the group (i.e. the grouping configured on the input pipe) that the record that is being processed belongs to.
in. refers to all records in the pipe called in.

_itemNumber is also a useful internal variable in ‘Calculate By Set’ streams. It is the order number of the record that is being processed within its group (i.e. within _this).

More information on the internal variables is here:
https://phixflow.atlassian.net/wiki/spaces/HELP73/pages/125141672/Internal+Variables

Example

I have the following 4 records in my input pipe:

A	B	C
1	Tree	Green
2	Tree	Brown
3	House	Red
4	House	Yellow

I group my input pipe on attribute B.

The records will be processed in order.
When I am processing record 1, _current refers to record 1. i.e. _current.C would be ‘Green’. _this refers to all records where B = Tree. i.e. _this.C would be the array [‘Green’,’Brown’]. _itemNumber = 1.
When I am processing record 2, _current refers to record 2. _this refers to all records where B = Tree. _itemNumber = 2.
When I am processing record 3, _current refers to record 3. _this refers to all records where B = House. _itemNumber = 1.
When I am processing record 4, _current refers to record 4. _this refers to all records where B = House. _itemNumber = 2.