A Quick Solution for Complicated Data Mapping Functions

Some data conversions require multiple steps to satisfy today's complex data communication requirements. Altova MapForce® 2017 lets you graphically combine a wide variety of mathematical, logical, string, and other specialized functions to build complicated "data equations" to get the results you need.

If you're working on a big, complicated mapping, if you're using an unfamiliar function for the first time, or when your function creates an intermediate result that needs further processing, it is helpful to test your work each step of the way as you build. You can use a simple text file as a temporary target to see the output of your function under construction.

Let's say we want to add a date and time stamp to an existing mapping of an output file to record the time the data was generated. We can easily experiment with the MapForce now function in a new mapping that simply connects now to a text file.

Simple function mapped to text file

Clicking the Output button at the bottom of the mapping window shows us the immediate result:

Output of now function

Okay, that works, but maybe the consumer of the output file would like to see a more elegantly formatted version.

We can work with various datetime and string functions and build a complicated function until our mapping looks like this:

complicated function mapped to text file

Or we could simply use the format-dateTime conversion function with a string constant to define the format:

Format dateTime funtion mapped to text file

As we work, we can press the Output button at any time, until we are satisfied with a result that looks like this:

Output of format dateTime function

If you don't work with dateTime data types every day, it's hard to write a format string on your first try that generates the desired result! After several attempts, variations, and consultations with MapForce Help, here's the format we developed and stored in the string constant connected to the format input of the format-dateTime conversion function:

Format specification string

So far we used a text file that was defined as a basic CSV file with only a single field, but you can open the file Properties dialog and add fields or create a more customized output file.

Component settings dialogn for text file

One advantage of using a text file as the output target is there is no type checking of the function result, so the text file target lets you examine and verify the output of any function. In the screenshot below we created a mathematical function and added a second field to the text file target to map the result.

Multiple functions mapped to two fields in a text file

Now the output is a single-row, comma separated result like this:

Output of multiple functions in CSV format

If our ultimate target is going to be a database field or an XML element with an integer data type, then we can immediately see this mathematical function doesn't produce the expected datatype.

When you have perfected your function in the test mapping, you can simply select, copy, and paste it directly into your larger mapping. You can then connect the output of the function to your ultimate target. In the screen shot below our format dateTime function is mapped to an element in an XML schema that is defined as a string.

Tested function pasted into a larger data mapping project

If this is a function you are likely to want again in the future, you can convert it to a User-Defined Function and save it in your library for convenient reuse.

Create User-defined Function menu option

You can even use a text file as a temporary target to build and test a complicated function right inside your larger mapping. In that case, your mapping includes multiple possible outputs. The eyeball button at the top right of each output file selects which output file is displayed in the Output window.

Text file as a temporary output target in a large mapping

When you select the text file as the Output preview, the MapForce built-in execution engine does not process the larger mapping, only the function connected to the text file. This can be a huge time-saver when you just want to examine the function result and your larger mapping processes a large volume of data, or if you don't want to risk inserting invalid data into a database, and in many other situations.