Format
Returns a string that is the result of formatting a value according to one or more specified styles.
Syntax
Format(Val [, Style [, Width [, Locale]]])
Parameters
Val
Type: None
The value to be formatted.
Style
Type: Text
Optional. One or more formatting codes. Multiple codes must be separated by semicolons.
Width
Type: Long
Optional. The minimum width of the formatted string.
Locale
Type: Long
Optional. The output locale to use for regional variations in formatting. If Locale is omitted or is negative, the current output locale is used for formatting.
(return)
Type: Text
Val formatted as a text string.
Remarks
Val is formatted according to Style, as described below. If Width is supplied and the formatted string is shorter than the absolute value of Width, the string is padded with space characters to make it up to (the absolute value of) Width characters. If Width is negative, spaces are added at the end of the string; otherwise, spaces are added at the start.
An error occurs if Val is an array with more than one dimension. Locale, if specified, is used for regional variations in formatting, but not for error messages. Custom formatting is not currently supported.
If the metadata to be used for formatting needs to provided by a separate variable from
Val, use
FormatValue.
Style code syntax
Style consists of one or more formatting codes. Multiple codes must be separated by semicolons. Each code is of the form C[p][=t], where:
▪C is a letter specifying the type of formatting to do.
▪p is an optional integer that modifies the formatting in some way, depending on C.
▪t is an optional text argument whose meaning depends on C.
If C is an unknown code, the formatting described under None in the table below is used (and no error occurs), using the values of p and t, if supplied. The argument p may not have a plus or minus sign, so it can only be a non-negative integer. If p or t is inappropriate for C, it is ignored (and no error occurs).
Applying a style
Except for the Count style (s, described in the table below), if Val is an array or a categorical value, the result is a comma-separated list of the results of applying the given style code (or codes) to each item or element in turn (in this case, “Val” in the table below refers to each individual item or element, not the whole array). No attempt is made to differentiate the separator character from any occurrences of the same character in the individual formatted values. This gives the same result for a categorical value as for an array of long values. If an array contains values of different types, the style code (or codes) may be interpreted differently for different items. If Width is specified, it is applied to the whole list, not to individual items.
Except for special cases described in the table below, the value is converted to the expected type, using the input locale where necessary to interpret strings. For an object, the value of the object’s default property is used (in this case, “Val” below refers to the retrieved value). If any error occurs in retrieving an object’s properties, or in converting a value to the expected type, the result for that value is “”, except where noted below.
Where formatting is affected by locale settings, the system settings for the locale are used, ignoring any “user overrides” (changes made to regional settings by the user currently logged in to the server).
Except for the Count style (s, described below), if Val is NULL, the result is an empty string ("").
Applying multiple styles
If Style consists of more than one formatting code, each code is applied in turn to Val as described in “Applying a Style” above. Because the result of applying the first code will always be a text value, subsequent codes should only be those that are relevant to text values, for example, LowerFirst, Count, or UpperAll (l, s, or U, described below).
Any padding required by Width is done after all codes have been applied.
Style code definitions
None
For an MDMLabeledObject, the same as for Label (b), using t if supplied.
For any other object, the same as for Value (v), using t if supplied.
For other types, the same as CText(Val), ignoring p and t.
a
Style name: Name
For an MDMLabeledObject, the Name property. If an error occurs retrieving the name, the result is “”.
For any other object, the result of applying the Name format to the object's default property value.
For other types, the Name of the MDM Element whose value is CLong(Val). If the MDM Element or its Name cannot be accessed, the Name of the MDM mapped category value whose value is CLong(Val). If the MDM mapped category value does not exist, the result is the same as for Decimal1 (d), using p if supplied.
A
Style name: NameFull
Same as for Name (a), but using the FullName instead of the Name for an MDMLabeledObject or MDM Element.
b
Style name: Label
For an MDMLabeledObject, its Label. If an error occurs retrieving the label, the result is “”.
For any other object, the result of applying the Label format to the object’s default property value.
For other types, the Label of the MDM Element (in the current MDM object) whose value is CLong(Val). If an error occurs retrieving the label, the result is the same as Decimal1 (d), using p if supplied.
Labels are retrieved using the current Context and Language in the metadata. If t is given, it’s the name of the LabelType to use. If t is omitted, or specifies a nonexistent LabelType, the current LabelType is used.
B
Style name: LabelFull
Same as for Label (b), but using the FullLabel instead of the Label for an MDMLabeledObject or MDM Element. The FullLabel can only be retrieved for the MDM document’s current LabelType, so t is ignored.
c
Style name: Currency
Val converted to double and formatted as a currency, using the currency symbol and format specified by the output locale. The number of decimal places is adjusted to p by rounding Val (away from zero, for borderline cases), or by adding zeros at the end. If p = 0, the result has no decimal separator and no fractional part. If p is omitted, the default for the output locale is used.
C
Style name: Currency-Code
Same as for Currency (c), but using the three-character international monetary symbol specified by the output locale.
d
Style name: DateShort, Decimal1
For a date value, short date format specified by the output locale (ignoring time of day in Val).
For other types, Val converted to long and formatted as follows:
▪A minus sign (-) if Val < 0. Followed by...
▪One or more decimal digits (0-9).
The string length is increased to p, if given, by inserting zeros before the first digit.
D
Style name: DateLong, Decimal2
For a date value, long date format specified by the output locale (ignoring time of day in Val).
For other types, same as for Decimal1 (d).
e
Style name: Scientific-Lower
Val converted to double and formatted as follows:
▪A minus sign (-) if Val < 0. Followed by...
▪One decimal digit (0-9). Followed by...
▪A decimal point (.). Followed by...
▪p decimal digits (0-9). Followed by...
▪Lower-case e. Followed by...
▪A plus or minus sign (+ or -). Followed by...
▪Three decimal digits (0-9).
The part before the e (the mantissa) is Val scaled by a power of 10 so that its absolute value is >= 1.0 and < 10.0, unless Val is 0.0. The number of decimal places is adjusted by rounding the mantissa (away from zero, for borderline cases), or by adding zeros at the end. If p = 0, the mantissa has no decimal separator and no fractional part. If p is omitted, it has 6 decimal places.
The part after the e (the exponent) is the power of 10 by which the mantissa must be multiplied to give Val. The exponent length is increased to 3 digits, if necessary, by adding zeros at the left. If Val is 0.0, the mantissa and exponent are both zero. If the absolute value of Val is > 0.0 and < 1.0, the exponent is negative, otherwise it is positive.
E
Style name: Scientific-Upper
Same as for ScientificLower (e), but with an upper-case E.
f, F
Style name: DateTime, FixedPoint
For a date value, long date and time formats specified by the output locale, separated by a space.
For other types, Val converted to double and formatted as follows:
A minus sign (-) if Val < 0. Followed by...
One or more decimal digits (0-9). Followed by...
A decimal separator (specified by the output locale). Followed by...
p decimal digits.
The number of decimal places is adjusted by rounding Val (away from zero, for borderline cases), or by adding zeros at the end. If p = 0, the result has no decimal separator and no fractional part. If p is omitted, the default for the output locale is used.
l
Style name: LowerFirst
CText(Val), with the first alphanumeric character converted to lowercase (if it is an uppercase letter) according to the rules for the output locale.
L
Style name: LowerAll
CText(Val), with all alphabetic characters converted to lower case according to the rules for the output locale.
n, N
Style name: Fixed-Grouped
Val converted to double and formatted as for FixedPoint1 (f), but with digits before the decimal separator grouped according to the rules for the output locale.
p, P
Style name: Percent
Val converted to double and multiplied by 100.0, formatted as for FixedPoint (f), followed by a percent sign (%).
s, S
Style name: Count
For an array or categorical value, the number of items/elements it contains.
For text, the number of characters in the string.
For NULL, 0.
For other types, 1.
For all types, the count is formatted as for Decimal1 (d), using p if supplied.
t, T
Style name: Time
Val converted to date and formatted according to the time format specified by the output locale (ignoring the date part of Val).
u
Style name: UpperFirst
CText(Val), with the first alphanumeric character converted to uppercase (if it is a lowercase letter) according to the rules for the output locale.
U
Style name: UpperAll
CText(Val), with all alphabetic characters converted to upper case according to the rules for the output locale.
v
Style name: Value
For an object value, the value of a specified property, converted to text. If t is given, it is the name of the property to use. If it is omitted, or the value of the specified property can not be retrieved, the default property of the object is used, if it exists. If no suitable property is found, the result is “”. The first property that returns a value is used, even if the value it returns is “”.
For other types, Val converted to long and formatted as for Decimal1 (d), using p if supplied.
V
Style name: ValueNative
For an object value, the value of a specified property (same as for Value (v)).
For other types, the native value (for the current DataSource in the metadata) of the MDM ElementInstance whose value is CLong(Val), formatted as for Decimal1 (d), using p if supplied. If the native value can’t be retrieved, the result is “”.
w
Style name: TitleFirst
CText(Val), with the first alphanumeric character converted to upper case, and the rest of the string converted to lower case according to the rules for the output locale.
W
Style name: TitleAll
CText(Val), with the first character of each word (any alphanumeric character that is not preceded by an alphanumeric character) converted to upper case, and the rest of the string converted to lower case according to the rules for the output locale.
x
Style name: HexLower
Val converted to long and formatted as one or more hexadecimal digits (0-9 and a-f).
Val is treated as an unsigned value; this means that if Val < 0, the value formatted is equivalent to Val + Pow(2, 31). Thus the range of possible non-negative long values (0 to 2147483647) is formatted as “0” to “7fffffff”, and the range of negative values (-2147483648 to -1) is formatted as “80000000” to “ffffffff”. This is consistent with the hexadecimal syntax for expressing integers in mrScript, eg &H80000000 = -2147483648.
The string length is increased to p, if given, by inserting zeros before the first digit.
X
Style name: HexUpper
Same as for HexLower (x), but with upper-case A-F for the hexadecimal digits.
Most of the above style codes are based on format specifiers used in .NET, with some extensions. For locale-specific formatting rules, see Globalization Step-by-Step: Use Locale Model:
Examples
1. Using the Format function in mrScriptBasic
Note Many of the examples in the table below make use of the Household sample metadata document, household.mdd, that is installed with the DDL.
|
Function call
|
Result
|
Notes
|
|
Format(CDate("2004-12-02 15:49:31"), "f", , 1046)
|
"quinta-feira, 2 de dezembro de 2004 15:49:31"
|
Brazilian Portuguese DateTime format
|
|
Format(CDate("2004-12-02 15:49:31"), "f", , 1052)
|
"2004-12-02 3:49:31.MD "
|
Albanian DateTime format
|
|
Format(MDM.Fields["person.trip.purpose"], "a")
|
"purpose"
|
The Name property
|
|
Format(MDM.Fields["person.trip.purpose"], "A")
|
"person[..].trip[..]. purpose"
|
The FullName property
|
|
Format(MDM.Fields["person.trip.purpose"], "b")
|
"Purpose of trip"
|
The Label
|
|
Format(MDM.Variables["person[2].tvdays[{Channel_3}].Column" ], "a")
|
"Column"
|
The Name property
|
|
Format(MDM.Variables["person[2].tvdays[{Channel_3}].Column" ], "A")
|
"person[2].tvdays[ {Channel_3}].Column"
|
The FullName property
|
|
Format(MDM.Variables["person[2].tvdays[{Channel_3}].Column" ], "b")
|
"Number of days watched last week"
|
The Label
|
|
Format(MDM.Variables["person[2].tvdays[{Channel_3}].Column" ], "B")
|
"2,{Channel_3} : Number of days watched last week"
|
The FullLabel
|
|
Format(MDM.Fields["person.newspapers"], "v=EffectiveMaxValue")
|
"12"
|
Value of the EffectiveMaxValue property
|
|
Format(MDM.CategoryMap, "v=Count")
|
"125"
|
Value of the Count property
|
|
Format(MDM.Variables["person[6].occupation"], "V=hascasedata")
|
"True"
|
Value of the Hascasedata property
|
|
Format(MDM, "V=URL")
|
"[INSTALL_FOLDER]\UNICOM\Intelligence\<version>\DDL\Data\Data Collection File\household.mdd"
|
Value of the URL property
|
|
Format(MDM.URL, "L")
|
"[INSTALL_FOLDER] \UNICOM\Intelligence\<version>\ddl\data\data collection file\household.mdd"
|
Converted to all lower case
|
|
Format(MDM.URL, "U")
|
"[INSTALL_FOLDER] \UNICOM\Intelligence\<version>\DDL\DATA\DATA COLLECTION FILE\ HOUSEHOLD.MDD"
|
Converted to all upper case
|
|
MDM.MDMVersion.Split(".").Format("d4")
|
"0002,0010,0000,1664"
|
Formatted as 4 decimal digits
|
|
Format(MDM.Variables["person[2].tvdays[{Channel_3}].Column" ], "b;l")
|
"number of days watched last week"
|
The result of applying two style codes. The first returns the variable's Label as a text string, and the second converts the text string to all lower case.
|
2. Using the Format function in an SQL query
This example shows the use of the Format function in an SQL query that can be run against the UNICOM Intelligence Data File version of the Household sample data set, household.ddf. The function is used to display the value of a decimal variable called Satisfaction as a fixed point decimal and as a percentage figure:
SELECT DISTINCT
satisfaction,
satisfaction.Format(’f2’) AS FixedPoint,
Format(satisfaction / 10.0, ’p0’) AS Percentage
FROM hdata.person.trip
Here is the result set:
satisfaction FixedPoint Percentage
---------------- ---------- ----------
8.5 8.50 85%
9 9.00 90%
7.75 7.75 78%
8 8.00 80%
7.5 7.50 75%
9.5 9.50 95%
10 10.00 100%
5 5.00 50%
8.30000019073486 8.30 83%
7.90000009536743 7.90 79%
8.19999980926514 8.20 82%
6.30000019073486 6.30 63%
5.5 5.50 55%
This next example shows another SQL query that can be run against the Household sample data set, household.ddf. The Format function is used to display the value of a categorical variable called Country in five alternative formats:
SELECT DISTINCT
country,
country.Format(’v2’) AS Value,
country.Format(’a’) AS Name,
country.Format(’b’) AS Label,
country.Format(’b;U’) AS LabelUpperCase,
country.Format(’s4’) AS [Count]
FROM hdata.person.trip
Here is the result set:
country Value Name Label LabelUpperCase Count
--------------- ----- ------------- ------------- -------------- -----
{united_states} 85 United_States United States UNITED STATES 0001
{switzerland} 83 Switzerland Switzerland SWITZERLAND 0001
{india} 68 India India INDIA 0001
{portugal} 78 Portugal Portugal PORTUGAL 0001
{holland} 67 Holland Holland HOLLAND 0001
{morocco} 75 Morocco Morocco MOROCCO 0001
{australia} 57 Australia Australia AUSTRALIA 0001
{france,spain} 64,81 France,Spain France,Spain FRANCE,SPAIN 0002
{china,japan} 61,71 China,Japan China,Japan CHINA,JAPAN 0002
{germany} 65 Germany Germany GERMANY 0001
The first column shows the categorical variable without any formatting, the second column shows the categorical values, the third and fourth columns show the values of the Name and Label properties respectively, the fifth column shows the result of applying two style codes, and the sixth column shows the number of elements in the categorical variable.
3. Using the Format function in an interview script
The next example shows the Format function being used in an interview script that will run in version 3.0 (or later) of UNICOM Intelligence Interviewer - Server Admin. The Format function is used to display the labels and formatted values of the categories selected in the LikeColors question:
Metadata(en-US, Question, label)
LikeColors "Which colors do you like?" categorical
{
red "I like Red",
blue "I like Blue",
green "I like Green",
yellow "I like Yellow",
pink "I like Pink",
purple "I like Purple",
orange "I like Orange"
};
LikeColorsResponse "{InsertHere}" info;
End Metadata
Routing(Web)
LikeColors.Ask()
Dim labels, values, str
labels = LikeColors.Format("b") ' labels of responses
labels = labels.Replace(",", mr.CrLf)
values = LikeColors.Format("d3") ' response values with leading zeroes
str = "You chose these:" + mr.CrLf + labels + mr.CrLf
str = str + "Their numerical values are (" + values + ")."
LikeColorsResponse.Ask(str)
End Routing
Depending on the response to the LikeColors question (and your browser environment), the LikeColorsResponse question might appear like this:
See also
