Insert with pivot transposition
Function and purpose of the operation¶
The insert with pivot transpose operation adds as many columns to the Target dataset as there are values to transpose.
On the operation definition page, you must specify the fields of the source dataset:
- Pivot attribute field: the source field from which the data to be transformed into target fields will be extracted
- Pivot value field: the source field from which data will be taken to be aggregated, by means of an aggregation function to be specified into the fields extracted by processing the pivot attribute field.
- Aggregation function: aggregation function for the pivot value field.
Note: the target field of a matching field cannot coincide with a pivot target field
Note: pivot attributes that are not present in the target dataset will be ignored.
Advanced Tab¶
The source scenario /period fields allow you to define a scenario/period from which to read data for the given source dataset. It is possible to specify a value using the AW parameters or through the same syntax used in the definition of cell fields.
When the field is left blank, the DTP launch scenario/period is taken into account; however, in the case of a non-existent or inactive scenario/period, the DTP will go ahead without running the operation, reporting the failed execution in the audit.
Note: the advanced tab is only visible for physical or virtual datasets.
Example 1: base case without matching fields
Source Dataset¶
| PRODUCT | CLIENT | COD_VALUE | VALUE |
|---|---|---|---|
| P1 | C1 | QUANTITY_03 | 10 |
| P1 | C1 | QUANTITY_06 | 20 |
| P1 | C1 | QUANTITY_09 | 30 |
| P1 | C1 | QUANTITY_12 | 40 |
| P2 | C1 | QUANTITY_03 | 50 |
| P2 | C1 | QUANTITY_06 | 60 |
| P2 | C1 | QUANTITY_09 | 70 |
| P2 | C1 | QUANTITY_12 | 80 |
On the operations page, the following items are defined:
- Source pivot field: COD_VALUE
- Pivot value source field: VALUE
- Pivot value source field - grouping mode: SUM
Target Dataset¶
| QUANTITY_03 | QUANTITY_06 | QUANTITY_09 | QUANTITY_12 |
|---|---|---|---|
| 60 | 80 | 100 | 120 |
If there are no matches for a tuple for a given transposed column, no value will be displayed. See example 2 tuple (2025 | Q2) transposed column NORTH.
Example 2: Case with matching fields
Source Dataset¶
| YEAR | QUARTER | REGION | SALES |
|---|---|---|---|
| 2024 | Q1 | NORTH | 200 |
| 2024 | Q1 | SOUTH | 400 |
| 2024 | Q1 | NORTH | 300 |
| 2024 | Q2 | NORTH | 700 |
| 2025 | Q1 | SOUTH | 800 |
| 2025 | Q1 | NORTH | 500 |
| 2025 | Q1 | SOUTH | 500 |
| 2025 | Q2 | SOUTH | 300 |
On the operations page, the following items are defined:
- Source pivot field: REGION
- Pivot value source field: SALES
- Pivot value source field - grouping mode: SUM
Matching fields¶
| Target field | Source | Grouping mode |
|---|---|---|
| YEAR | YEAR | GROUP BY |
| QUARTER | QUARTER | GROUP BY |
| GAIN | GAIN | SUM |
Target Dataset¶
| YEAR | QUARTER | NORTH | SOUTH | GAIN |
|---|---|---|---|---|
| 2024 | Q1 | 500 | 400 | 600 |
| 2024 | Q2 | 700 | 500 | |
| 2025 | Q1 | 500 | 1300 | 1100 |
| 2025 | Q2 | 300 | 200 |