In the separation data can be restructured. This restructuring creates a new data set that is structured differently from the original one. To configure the separation, 2 tabs are available, Basis contains the basic values, on Settings the Grouping Columns (the fixed columns) are selected. From the remaining columns the new rows are generated, while the grouping columns are carried over. If no grouping columns are selected in the settings, the data set is taken over unchanged.
Note: The separation corresponds to the pivot function in Excel.
For better understanding an example:
The input table:
| ID | Name | Color | Weight |
|---|---|---|---|
| 1 | Chair | Red | 7 |
| 2 | Table | Blue | |
| 3 | Lamp | 2 |
The goal is to transform this into a list of attribute rows: ID | attribute_name | attribute_value.
For this we assign a name on the tab ‘Base’ and name the target attribute attribute_value. We also fill the Source attribute with attribute_name.
On the tab ‘Settings’ the grouping columns (the fixed columns) must be moved to the right, in this case ID. The remaining columns (Name, Color, Weight) are pivoted automatically.
As a result, after the execution, the following table is created:
| ID | attribute_name | attribute_value |
|---|---|---|
| 1 | Name | Chair |
| 1 | Color | Red |
| 1 | Weight | 7 |
| 2 | Name | Table |
| 2 | Color | Blue |
| 3 | Name | Lamp |
| 3 | Weight | 2 |
In the attribute_name column the origin of the value is recorded.
If you want empty fields to appear in the target record, use the ‘Accept empty fields’ button. The settings remain the same as in the previous example, in addition the toggle ‘separate empty fields’ is set.
The execution of the separation results in this table:
| ID | attribute_name | attribute_value |
|---|---|---|
| 1 | Name | Chair |
| 1 | Color | Red |
| 1 | Weight | 7 |
| 2 | Name | Table |
| 2 | Color | Blue |
| 2 | Weight | |
| 3 | Name | Lamp |
| 3 | Color | |
| 3 | Weight | 2 |
If the Source attribute is omitted while ‘Accept empty fields’ is enabled, identical rows can be created (for example, multiple empty values for the same ID). In this case Chioro removes the duplicates.