SELECT.UNIQUE
- Morten Lindequist Køhler
Selects content from the neutral Table-format.
Properties
From | Name of the Input Data Source.The input-source must contain the neutral Table-format. |
To | Name of the Output Data Source. |
Parameters
@FILTER | Filter the result of the selected data. See Command Maps & Parameters for more information. |
@ORDER | Order the result of the selected data. See Command Maps & Parameters for more information. |
Map
From | Name of Header, as it is named from Input-source. |
To | Name of the Header as it should be called in the Output-source. |
Script | Any Script code to execute for this header |
Guide
This Command is very similar to the SELECT Command, with a very important difference. It defines the first column in the Output-source to be unique. This means, that if a duplicate entry turns up after mapping the input-source, it will write to the same Output-row.
The most obvious application of this will be to eliminate duplicates. However, this behavior has some other very interesting applications for transforming data, which is best illustrated by the example below.
Example 1
The inner workings of both the SELECT and SELECT.UNIQUE Commands are to evaluate every entry in the Map for each row in the Input-source. This will normally generate an Output-row for every Input-row. However, with the SELECT.UNIQUE Command it will be less if duplicates are found.
Consider an input like the one below.
The above scenario is found very often. The same Item is repeated on multiple rows, with a new feature and value on each row, and in this case one of the features – ShortText - is even included in multiple languages.
By using SELECT.UNIQUE and defining a mapping as follows:
It will produce the following result:
For every input-row, it will find the same ItemNumber multiple times, and therefore target the same output-row. For every input-row, the Value will be different, and the real trick here is that the name of the Output-column will be different for every value as well, which means that SELECT.UNIQUE will create new columns in the Output-source to accommodate the different names.
Notice that To is defined as {Name}{Language}. Whenever these curly brackets are used, they will be interpreted as a column-name from the Input-source, thereby referencing the value from this input-column, in this case combining the Name 'ShortText' with '' with the Language 'EN' and producing 'ShortText_EN'.
Also notice that a small Script is used to Trim '_' from the end of the Names, since all Output-columns that did not have a Language would otherwise have this character at the end.
Mapping Lookup References
The Lookup references as introduced above, such as e.g. {Name}_{Language} can be used by for both SELECT and SELECT.UNIQUE Commands.
Whenever a curly bracket is used it will always perform lookup from the Input-source. However, there is a very small difference when used within the From and the To properties.
To: Everything is interpreted as static content, unless it is contained in curly brackets. Furthermore, if it is blank, the Output-column name will be deferred from the Input-column name.
From: Content is only interpreted as static if it is contained outside of curly brackets. This means that curly brackets need to be used in order to include static content, such as e.g. {Level1}|{Level2}|{Level3}, which will include a static '|' between each of the lookups to Level1, 2 and 3, concatenating the values from these columns with a '|' (e.g. ready to import as a hierarchy in Perfion).
This also means that, if no curly brackets are included in From, it will be interpreted as one single lookup-name (i.e. to avoid having to enter curly brackets all the time, since the majority of map entries will be simple single lookups).