Insert with unpivot transposition
Function¶
The Insert with unpivot transposition operation allows you to add as many rows in the target dataset as there are columns in the source dataset to be transposed, as defined on the matching fields page.
On the operation definition page, you must specify the fields of the target dataset to be associated with the pivot attribute and the pivot value, which must obviously be separate fields.
It is mandatory to define matching fields, as at least one pivot field must be specified, along with its alias
Note: Field values that are not listed on the matching fields page will not be brought into the target table.
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.
Example 1: basic case: one attribute, one pivot and one alias
Source Dataset¶
| PRODUCT | CLIENT | JUNE | DECEMBER |
|---|---|---|---|
| Prod 01 | Client 01 | 15 | 30 |
| Prod 02 | Client 02 | 18 | 9 |
Matching Fields Table¶
| Relationship type | Details |
|---|---|
| Attribute | Target field: PRODUCT, Source: PRODUCT |
| Pivot | Pivot value: JUNE, Pivot alias: PERIOD_JUNE |
| Pivot | Pivot value: DECEMBER, Pivot alias: PERIOD_DECEMBER |
¶
In operation page are defined:
- Pivot Attribute Fields: ATTRIBUTE
- Pivot Value Fields: VALUE
Target Dataset¶
| PRODUCT | ATTRIBUTE | VALUE |
|---|---|---|
| Prod 01 | PERIOD_JUNE | 15 |
| Prod 01 | PERIOD_DECEMBER | 30 |
| Prod 02 | PERIOD_JUNE | 18 |
| Prod 02 | PERIOD_DECEMBER | 9 |