Add a column holding the row-by-row delta of another column. Every new unique combination of Key fields will reset the delta calculation to 0.
Example
Example Input
| date | key_1 | key_2 | imp |
| 2018-01-01 | a | b | 100 |
| 2018-01-01 | a | c | 5 |
| 2018-01-01 | b | b | 3 |
| 2018-01-02 | a | b | 200 |
| 2018-01-02 | a | c | 0 |
| 2018-01-02 | b | b | 9 |
| 2018-01-03 | a | b | 250 |
| 2018-01-03 | a | c | 23 |
Example Output
| key_1 | key_2 | date | imp | imp_increment |
| a | b | 2018-01-01 | 100 | 100 |
| a | b | 2018-01-02 | 200 | 100 |
| a | b | 2018-01-03 | 250 | 50 |
| a | c | 2018-01-01 | 5 | 5 |
| a | c | 2018-01-02 | 0 | -5 |
| a | c | 2018-01-03 | 23 | 23 |
| b | b | 2018-01-01 | 3 | 3 |
| b | b | 2018-01-02 | 9 | 6 |
(Dashed borders are for illustrative purposes only, and show where the increment delta has been reset).
I Ain't Afraid of No Code!
[
[
"increment",
{
"keys": [
"key_1",
"key_2"
],
"subtable": null,
"fieldname": "imp_increment",
"sourcefield": "imp",
"_comment": null
}
]
]param list keys: Define a key consisting of multiple fields as discriminator. Resets the initial value of the delta (starts with value of current row).
param str subtable: Name a subtable in which output will be stored (will create subtable where necessary).
param str fieldname: Name of the output field that will be created to hold the deltas.
param str sourcefield: Column on which the delta should be calculated. Must be of type number.
param str comment: A comment that will appear in the Transformation UI.
Comments
0 comments
Article is closed for comments.