| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
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, in a group in a
stream, ortable create a cumulative total per group. You get the same number of records as you put in.
A calculate-by-set stream table 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 table 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 setrecordset, then you should prefix your attribute expression with the pipe name. For example, min(in.StartDate) would return the earliest StartDate in the stream setrecordset.
Unlike all other stream table types, records in a stream set recordset 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.
| Infonote |
|---|
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 tables are often used when you want to create a cumulative value across the whole streamtable. This is done in conjunction with the cache function.
Further Details onInternal 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.
For Calculate-By-Set
The following internal variables are available in calculate-by-set tables
- in. refers to all records in the pipe called in.
- _itemNumber
- : the order number of the record that is being processed within its group (i.e. within _this).
https://phixflow.atlassian.net/wiki/spaces/HELP73/pages/125141672/Internal+Variables
- _current: the record that is being processed. Only used in calculate-by-set tables.
- _this: the group (that is, the grouping configured on the input pipe) that the record that is being processed belongs to. Only used in calculate-by-set tables.
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.