4.4.4.7. Working with formulas - frequent scenarios – Babelway

This section explains in detail some frequent scenarios when working with formulas. It gives a lot of examples, and also emphasizes some traps in which you should not fall.

Working with numbers

When you have to do some calculations, you must always keep in mind that all fields from your source and target documents are Strings, not Numbers.. To be able to make calculations, xpath (like any programming language) needs Numbers. If your values are Strings, they first have to be converted into Numbers, even if this conversion can be implicit in some cases.

Babelway recommends that you always explicitly convert your input fields with the toNumber function.. As an example, the following call will perfectly work for all values.

toNumber([yourInputField]) div 100

We can show some results for some input values.

toNumber('100')*0.1  -  '10'
toNumber('30507')*0.1  -  '3050.7'

When you have to convert the results of your calculations (Numbers) to Strings (as expected by the target field), it is safe to use implicit conversion. The system will automatically convert your number to its more natural representation (the one without useless zeros).

toNumber('100.00')*0.100)  -  '10'

You only need to make an explicit conversion if you want to use another representation. For example, the following call will ensure that target number is always displayed with 2 decimals.

formatNumber(toNumber('100')*0.1, '0.00')  -  '10.00'

Below, find some problems you could encounter should you not follow these recommendations:

Using xpath implicit conversions or conversions via the xpath number() function could lead to numeric problems.
```
number('30507')*0.1  -  '3050.7000000000003'
```

Display in scientific notation.

number('1234567890')*100  -  '1.23456789E11'

Note: For all supported xpath Numeric types (xs:decimal, xs:integer, xs:float, xs:double) you can check the below xpath specifications link for more details.

https://www.w3.org/TR/xpath-functions-31/#numeric-types

In some old mappings, you could see uses of a function named bif:cast. This function was added automatically in some cases to try to correct incorrect numbers, having the problems as described in this section. You should just check (or change) the formula so that it follows the recommendations of this section, and remove all the calls to bif:cast.

Moreover, bif:cast simply has no effect when its argument is not a Number, and it can be removed safely in all of these situations.

Changing date format

If you receive in your input document dates/times in one format, and want to write them in the output document in another format, it can be easily achieved using the changeDateTimeFormat function.

changeDateTimeFormat([yourInputField], [yourInputFormat], [yourOutputFormat])

All details about the date formats can be found on https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/text/SimpleDateFormat.html.

Some examples :

changeDateTimeFormat('01/01/2015', 'dd/MM/yyyy', 'yyyyMMdd')  - '20150101'
changeDateTimeFormat('01/01/2015 12:34:56', 'dd/MM/yyyy hh:mm:ss', 'yyyyMMddhhmmss')  - '20150101123456'
changeDateTimeFormat('01/01/2015 12:34:56', 'dd/MM/yyyy hh:mm:ss', 'dd-MM-yyyy')  - '01-01-2015'
changeDateTimeFormat('30/02/2015', 'dd/MM/yyyy', 'dd-MM-yyyy')  - Error

This function can also be used to extract almost any information about a DateTime

changeDateTimeFormat('01/01/2015', 'dd/MM/yyyy', 'EEEE')  - 'Thursday'

The list of all options usable in the format parameters can be found on https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/text/SimpleDateFormat.html

This function will also do the job very easily if the input can be in different formats. You just have to enumerate the possible formats, separated by a comma, in the input format.

changeDateTimeFormat('01/01/2015', 'dd/MM/yyyy,yyyyMMdd', 'yyyyMMdd')  - '20150101'
changeDateTimeFormat('20150101', 'dd/MM/yyyy,yyyyMMdd', 'yyyyMMdd')  - '20150101'

If you have many transformations to do, with always the same formats, it can help to define a more convenient user function, to avoid having to write the formats at every call. This is explained in detail in the sections about user functions.

convertDate('01/01/2015')  - '20150101'

Making Date/Time calculations

As for making calculations with numbers, you have to remember that fields from input and output documents are Strings, and you will have to convert them to DateTime objects to be able to make your calculations.

You can get DateTime objects by creating them from their representation, via the parseDateTime function. You can also receive DateTime object via some methods like currentDateTime

The conversion of a DateTime to its representation can be done via the formatDateTime function.

When you have DateTime values, there are many functions allowing to make some calculations (ex: addDays )

formatDateTime(addDays(parseDateTime('2014-06-28', 'yyyy-MM-dd'), 3), 'yyyy-MM-dd') - '2014-07-01'

Counter

The following techniques can be used if you need to implement a counter.

Xpath functions

You can use xpath formulas. Here are some examples :
- The function position() gives you the index of the iteration in the loops. It can be used to number every row.
- Xpath functions like count(preceding-sibling::*) allow you to count position of an element within a list of selected elements.
Metadata

You can define a metadata, and increment it at each call. It will return successfully values 0, 1, 2, 3, ...

You can use the function nextCounterValue(counterName) to do all the job for you. It will increment the value of the metadata by 1, and return the new value.
Lookup table

The two previous techniques are simple, but will only work inside a given message. They will not work across multiple messages. If you need to implement a persistent counter, you can work with a lookup table :
- Create a lookup table named 'counters' with two columns named 'name, value'.
- Add an entry (mycounter, 0).
- In your mapping, use the function incrementLookupTableValue('counters', 'name', 'value', 'mycounter'). It will automatically increment the value, and return the new value.

Getting Information From An External HTTP Ressource

Babelway offers specialized transformation functions to call external web services. This is useful to complement the message data and lookup tables with external source of information.

You can reach HTTP ressources using one of the 3 following functions: httpGet, httpPost or httpSoap. You will get a string representation of the response. Moreover, these function can be associated with xmlToXmlNode or jsonToXmlNode to get a usable tree representation of the response that can be directly used in other Xpath functions.

upper-case(jsonToXmlNode(httpGet(concat('http://www.telize.com/geoip/', IP)))//*:country) - BELGIUM

Calling HTTP ressource

Please note that the Xpath expression selecting a node in the resulting tree needs to handle namespaces by adding a *: in front of the xml name of the node, like *:country in the example above. You need to do this even if the resulting tree is not part of a namespace, like in Json processing.

Storing Json or Xml in a lookup table

Lookup tables are very versatile, they can hold up to 10 columns. Stored data can be simple but can also be structured in Json or in Xml. This feature, in combination with the power of Xpath offers you endless possibilities to store and retrieve the data you need.

The best practice is to use one column for each data you want to search on. That makes up to 9 indexes and the last column to store the Xml or Json.

Xml in lookup table

You can use the 'Create data import channel' button of your lookup table to help you importing your data. Take a look at the xmlNodeToXml function. It will help you serializing a piece of your Xml before you store it in the content column.

xmlNodeToXml(Country) -

Import Xml in lookup table

Once the Xml or the Json is retrieved from the lookup table, it can be parsed with one of the following functions xmlToXmlNode or jsonToXmlNode to get a usable tree representation directly usable in other Xpath functions.

xmlToXmlNode(lookupTableValue('testXML', 'ip', 'xml', key))//*:country - BELGIUM

Use Xml from lookup table