FORMAT macro

The FORMAT macro is available in both Unica Campaign and Unica Interact (from version 12.1.1).


Format has two forms, one for numeric datatypes and one for text/character datatypes.

For numeric datatypes:

FORMAT(colName, width [, precision [, format_type [, alignment [, padding]]]])

For text/character datatypes:

FORMAT(colName, width [, alignment])
Note: In case of Unica Interact, width is an optional parameter.



The macro examines colName and determines its datatype, then imposes the appropriate rules for subsequent parameters accordingly.


Width should be large enough to hold the complete result, otherwise the result will be truncated. Acceptable values are from 1 to 29 if colName is numeric, otherwise from 1 to 255.
Note: In case of Unica Interact, in order to override the maximum width limit for TEXT type (255), a JVM parameter ‘-Dcom.unicacorp.interact.maxStringLengthInFormatMacro’ with default value as 255 is introduced.


Precision is number of digits after the decimal point. Acceptable values are from 0 to 15. If it's zero, then the result is integer. Default precision value is 2.


Valid keywords for format_type are:

PERIOD Period(.) is used as decimal symbol. No digit grouping symbol is used. This is the default value.
COMMA Comma(,) is used as decimal symbol. No digit grouping symbol is used.
PERIOD_COMMA Period as decimal symbol and comma as digit grouping symbol.
COMMA_PERIOD Comma as decimal symbol and period as digit grouping symbol.


Valid keywords for alignment are LEFT and RIGHT. Default value is RIGHT for numeric datatypes and LEFT for text/character datatypes.


Valid keywords for padding are SPACE and ZERO. Default value is SPACE. ZERO is ignored (and instead SPACE is used) if alignment is LEFT.

Note that numeric strings held within a text/character datatype are treated as text/character. Also note that the numeric form takes multiple optional keywords, each with a default value. However, to override the default of second or subsequent optional keywords you MUST code the defaults for the preceding optional keywords (in effect they become required). For example: to override alignment to be LEFT you must code: FORMAT(myNumCol, 10, 2, PERIOD, LEFT).


FORMAT converts numeric data to a string form with various formatting options to control and define the output string. This will be especially useful for creating Snapshot files with specific formats for mailing file purposes.


The following example defines a derived field using FORMAT.

The following examples show the same field, AvgMthPayment, in three formats.


Formatted using FORMAT(Behavior.AvgMthPayment,10,2,right,space):

Formatted using FORMAT(Behavior.AvgMthPayment,10,4):