Grouping and Referencing Data Using Calculate-By-Set
- Fiona Sargeant (Unlicensed)
- Anthony George
- david.pearson
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, in a group in a table create a cumulative total per group. You get the same number of records as you put in.
A calculate-by-set 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 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 recordset, then you should prefix your attribute expression with the pipe name. For example, min(in.StartDate) would return the earliest StartDate in the recordset.
Unlike all other table types, records in a 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.
it may make more sense to use a sequence, rather than _itemNumber in some scenarios, where you want to number records.
Calculate-by-set tables are often used when you want to create a cumulative value across the whole table. This is done in conjunction with the cache function.
Internal Variables 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).
- _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.