The Value-Map component enables you to replace a value by another value with the help of a predefined look-up table. Such a component processes only one value at a time; therefore, it has one input and one result on the mapping.
A Value-Map is very useful when you would like to map individual items within two sets in order to replace items. For example, you could map the days of the week expressed as numbers (1, 2, 3, 4, 5, 6, and 7) to the name of each day of the week ("Monday", "Tuesday", and so on). Likewise, you could map the month names ("January", "February", "March", etc) to the numeric representation of each month (1, 2, 3, etc). At mapping run time, the matching values will be replaced according to your custom look-up table. The values in both sets can be of different type, but each set must store values of the same data type.
Value-Map components are suitable for simple look-ups, where each value in the first set corresponds to a single value in the second set. If a value is not found in the look-up table, you can either replace it with a custom value or an empty value, or pass it on as is. If you need to look up or filter values based on more complex criteria, use one of the filtering components instead.
Importantly, when you generate code or compile a MapForce Server Execution file from the mapping, the look-up table data is embedded into the generated code or file. Consequently, defining a look-up table directly on the mapping is a good choice only if your data does not change frequently and is not very big (less than maybe a few hundred entries). If the look-up data changes regularly, you may find it difficult to maintain both the mapping and the generated code regularly—it is easier to maintain the look-up data as text, XML, database, or perhaps Excel.
If the look-up table is huge, the mapping execution will be slowed down by the look-up table. In this case, it is recommended to use a database component with SQL-Where instead. SQLite databases are good candidates for this, given their portability. On the server side, you can improve the performance of look-up tables by running a mapping with MapForce Server or MapForce Server Advanced Edition.
To add a Value-Map component to the mapping, do one of the following:
•Click the Insert Value-Map toolbar button.
•On the Insert menu, click Value-Map.
•Right-click a connection, and select Insert Value-Map from the context menu.
This adds a new Value-Map component to the mapping. You can now start adding pairs of items to the look-up table. To do this, double-click the component's title bar or right-click it and select Properties from the context menu.
At mapping run time, MapForce checks each value that reaches the input of the Value-Map. If there is a matching value in the left column of the look-up table, then it replaces the original input value with the value from the right column. Otherwise, you can optionally configure it to return one of the following:
•A replacement value. In the example above, the replacement value is the text "incorrect date". You can also set the replacement value to be empty, by not entering any text at all.
•The original input value. This means that, if no match is found in the look-up table, the original input value will be passed further on to the mapping, unchanged.
If you do not configure an "Otherwise" condition, the Value-Map returns an empty node whenever a match is not found. In this case, nothing will be passed to the target component and the output will contain missing fields. To prevent this from happening, you should either configure the "Otherwise" condition, or use the substitute-missing function.
There is a difference between setting an empty replacement value and not specifying the "Otherwise" condition. In the first case, the field will be generated in the output, but it will have an empty value. In the latter case, the field (or XML element) enclosing the value will not be created at all. For more information, see Example: Replacing Job Titles.
Populating a Value-Map
In a look-up table, you can define as many pairs of values as needed. You can enter the values manually, or copy-paste tabular data from text, CSV, or Excel files. Copy-pasting tables from an HTML page using a common browser will also work in most cases. You can also paste data from the database grid in the DB Query pane. If you copy data from text files, the fields must be separated by tab characters. In addition, MapForce will recognize text separated by commas or semicolons in most cases.
Keep in mind the following when creating look-up tables:
1.All items in the left column must be unique. Otherwise, it would not be possible to determine which item you want to match specifically.
2.Items that belong to the same column must be of the same data type. You can choose the data type from the drop-down list at the top of each column in the look-up table. If you need to convert Boolean types, enter the text "true" or "false" literally. For an illustration of this case, see Example: Replacing Weekdays.
If MapForce encounters invalid data in the look-up table, it displays an error message and highlights the invalid rows in pink color, for example:
To import data from an external source into the Value-Map component:
1.Select the cells of interest in the source program (for example, Excel). This can be either a single column of data or two adjacent columns.
2.Copy data to clipboard using the Copy command of the external program.
3.On the Value-Map component, click the row before which you would like to paste the data .
4.Click the Paste table from clipboard button on the Value-Map component. Alternatively, press Ctrl+V or Shift+Insert.
|Note:||The Paste table from clipboard button is enabled only if you have copied data from some source first (that is, if there is data on the clipboard).|
When your clipboard data contains multiple columns, then only data from the first two columns are inserted into the look-up table; any other subsequent columns will be ignored. If you paste data from a single column on top of any existing values, a context menu appears, asking whether the clipboard data should be inserted as new rows or the existing rows should be overwritten. Therefore, if you need to overwrite existing values in the look-up table as opposed to inserting new rows, ensure that the clipboard contains only one column, not multiple.
To insert rows manually before an existing row, first click the row of interest, and then click the Insert button.
To move an existing row to some other position, drag the row to the new position (upwards or downwards) while holding the left mouse button pressed.
To copy or cut rows for subsequent pasting at some other position, first select the row, and then click the Copy button (or Cut button, respectively). You can also copy or cut multiple rows that are not necessarily consecutive. To select multiple rows, hold the Ctrl key pressed while clicking the rows. Note that the cut or copied text always contains values from both columns; you cannot cut or copy values from one column only.
To remove a row, click it, and then click the Delete button.
To swap the left and right columns, click the Swap button.
Renaming Value-Map parameters
By default, the input parameter of a Value-Map component is called "input" and the output parameter is called "result". To make the mapping clearer, you can optionally rename any of these parameters by clicking the Edit button next to the respective name. The following is an example of a Value-Map with custom parameter names:
Previewing a Value-Map
After you have finished creating a Value-Map, you can quickly preview its implementation directly from the mapping by holding the mouse over the component's title bar: