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 columns to be separated are selected. From these selected columns the new rows are generated. If no attributes 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 output table:
| ID | Image1 | Image2 | Image3 | Type |
|---|---|---|---|---|
| 1 | dog1.jpg | dog1.jpg | dog3.jpg | dog |
| 2 | cat1.jpg | cat | ||
| 3 | donkey1.jpg | donkey2.jpg | donkey3.jpg | donkey |
| 4 | duck1.jpg | duck2.jpg | duck |
The columns Image1-3 contain names of images, 9 in total. There are also single empty fields, e.g. Image2 and Image3 are not used in the 2nd record.
Now the requirement is to generate an own record (row) for each image from the columns Image1-3. As a result, a data set with 9 entries is expected, one for each existing image.
For this we assign a name on the tab ‘Base’ and name the target attribute ‘image’. Source attribute remains empty. On the tab ‘Settings’ the attributes/column names Image1, Image2 and Image3 must be moved to the right, these are to be “rebuilt”.
As a result, after the execution, the following table is created:
| image | ID | type |
|---|---|---|
| dog1.jpg | 1 | dog |
| dog2.jpg | 1 | dog |
| dog3.jpg | 1 | dog |
| cat1.jpg | 2 | cat |
| donkey1.jpg | 3 | donkey |
| donkey2.jpg | 3 | donkey |
| donkey3.jpg | 3 | donkey |
| duck1.jpg | 4 | duck |
| duck2.jpg | 4 | duck |
As expected, there are 9 records, each image is now in one row. ‘ID’ and ‘Type’ have been taken over. However, there is no information about which column the generated value comes from. For ‘dog1.jpg’ there is no information that the entry originally comes from the column ‘Image1’.
To get this information into the new record, the Source Attribute field must be filled in on the Base tab, in this case ‘image-source-column’.
After another run of the separation, the result is as follows:
| image | ID | type | image-source-column |
|---|---|---|---|
| dog1.jpg | 1 | dog | image1 |
| dog2.jpg | 1 | dog | image2 |
| dog3.jpg | 1 | dog | image3 |
| cat1.jpg | 2 | cat | image1 |
| donkey1.jpg | 3 | donkey | image1 |
| donkey2.jpg | 3 | donkey | image2 |
| donkey3.jpg | 3 | donkey | image3 |
| duck1.jpg | 4 | duck | image2 |
| duck2.jpg | 4 | duck | image3 |
In the ‘image-source-column’ now the origin of the line is named.
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:
| image | ID | Type | image-source-column |
|---|---|---|---|
| dog1.jpg | 1 | dog | image1 |
| dog2.jpg | 1 | dog | image2 |
| dog3.jpg | 1 | dog | image3 |
| cat1.jpg | 2 | cat | image1 |
| 2 | cat | image2 | |
| 2 | cat | image3 | |
| donkey1.jpg | 3 | donkey | image1 |
| donkey2.jpg | 3 | donkey | image2 |
| donkey3.jpg | 3 | donkey | image3 |
| 4 | duck | image1 | |
| duck1.jpg | 4 | duck | image2 |
| duck2.jpg | 4 | duck | image3 |
The 12 resulting datasets now also contain lines for non-existing image entries. The 2nd and 3rd entry in the ‘Type’ cat differs only in the ‘Image source column’. So if the ‘image source column’ is omitted, two identical records would be created.
In this case Chioro removes the duplicates. So, if on the tab ‘Base’ the entry ‘Source attribute’ is missing and at the same time ‘Accept empty fields’ is set, the following table will be created:
| image | ID | Type |
|---|---|---|
| dog1.jpg | 1 | dog |
| dog2.jpg | 1 | dog |
| dog3.jpg | 1 | dog |
| cat1.jpg | 2 | cat |
| 2 | cat | |
| donkey1.jpg | 3 | donkey |
| donkey2.jpg | 3 | donkey |
| donkey3.jpg | 3 | donkey |
| 4 | duck | |
| duck1.jpg | 4 | duck |
| duck2.jpg | 4 | duck |
With the cat there would have been 2 identical lines, so the result is only 11 lines/records.