Syntax

Variables

VTL support two type of variable names, regular names and escaped names. Regular variable names in must start with a letter followed by letters, numbers and the underscore (_) characters. Escaped variable names must be enclosed by single quotes (') and can contain any characters except new lines. Single quotes can be escaped by doubling them.

variableName := value aVariableName1 := value '1Variable' := value 'variable''with''quotes' := value

Comments

Line comments are prefixed with two slashes ('//'). Block comments are surrounded by /* and */ and can contain new lines.

// Single line comment /* * A block * comment */

Data types

Integers

variable := 1 variable := -1 variable := 0

Floats

variable := -0.1e-10 variable := -0.1e10 variable := -0.1e+10 variable := 0.1e-10 variable := 0.1e10 variable := 0.1e+10 variable := +0.1e-10 variable := +0.1e10 variable := +0.1e+10 variable := 0.01 variable := -0.001 variable := +0.0001

Strings

variable := "STRING" variable := "STR""ING"

Booleans

variable := true variable := false

Dates and time

variable := 2000-01-01T00:00:00.000Z variable := 2000-01-01T00:00:00.000+00:15

Expression and operators

Precedence

One can adjust the precedence of expressions in VTL using the parenthesis (( and ))

variable := ( expression )

Arithmetic operators

The multiplication, division, addition and subtraction operators can be used with Float and Integer types.

variable := 1 + 2 / 3 * 4

If Floats and Integers are mixed in the same arithmetic expression the resulting number will be Float

floatVariable := 1 + 2.0 floatVariable := 1 * 1.0

Concatenation operator

The concatenation operator || concatenate two strings.

contac := [data] { result := left || " " || right } left[I,String],right[I,String] Hello,World null,World Hello,null

String functions

strings := [data] { withTrailingSpc := " " || value || " ", rightTrim := "[" || rtrim(withTrailingSpc) || "]", leftTrim := "[" || ltrim(withTrailingSpc) || "]", 'trim' := "[" || trim(withTrailingSpc) || "]", noTrim := "[" || withTrailingSpc || "]" } id[I,Long],value[M,String] 1L,Hello World!

Trim, ltrim and rtrim functions

The trim, ltrim and rtrim take a String as input and return a String.

The ltrim and rtrim functions remove leading (to the left and trailing (to the right) white characters from the given String, respectively.

The trim functions remove both the leading and trailing white characters.

    String   trim(String value)
    String  rtrim(String value)
    String  ltrim(String value)

Upper and lower function

    String  upper(String value)
    String  lower(String value)

The upper function transforms all of the characters of the given String to upper case. The lower functions transforms all the characters of the given String to lower case.

Mathematical functions

In addition to the arithmetic operators, basic mathematical operations are available as functions.

math := [data] { posInteger := data.value, negInteger := data.value * -1, posFloat := data.value / 10, negFloat := negInteger / 10, absFn := abs(negFloat), logFn := log(posInteger, posInteger), ceilFn := ceil(posFloat * 5), floorFn := floor(posFloat * 5), expFn := exp(posFloat), lnFn := ln(posInteger), logFn := log(posInteger, posInteger), powerFn := power(posInteger, posInteger), nrootFn := nroot(posInteger, posInteger), roundFn := round(posFloat, 2) } value[I,Long] 1L 3L 4L 5L 6L 7L 8L 9L 10L

Abs function

    Float   abs(Float value)
    Integer abs(Integer value)

The abs function takes an Integer or a Float value and returns its absolute value.

Ceil and Floor functions

    Integer floor(Float value)
    Integer ceil(Float value)

The ceil and floor functions take as input a Float value and return an Integer value.

ceil will return the smallest Integer value that is greater than the given value.

floor will return the largest Integer value that is smaller than the given value.

Trunc and Round functions

    Float trunc(Float value, Integer decimals)
    Float floor(Float value, Integer decimals)

The trunc and 'round' functions take as input a Float value and an Integer that represent a number of decimals. Both trunc and round return a Float.

trunc will wil truncate to the decimals whereas round will round up to the decimals.

Ln function (Napierian logarithm)

    Float ln(Float value)

Exp function

    Float exp(Integer value)
    Float exp(Float value)

Power function

    Float power(Float value, Float exponent)

Sqrt function

    Float sqrt(Float value)

Nroot function

    Float nroot(Float value, Float n)

Datasets

Record

Product

()

Join expressions

Inner join

innerJoin := [inner left, right] { filter true } id[I,String],measure[M,String],attribute[A,String] 3,left value 3, left attribute 3 4,left value 4, left attribute 4 5,left value 5, left attribute 5 6,left value 6, left attribute 6 id[I,String],measure[M,String],attribute[A,String] 1,right value 1, right attribute 1 2,right value 2, right attribute 2 3,right value 3, right attribute 3 4,right value 4, right attribute 4

Outer join

outerJoin := [outer left, right] { filter true } id1[I,String],id2[I,String],measure[M,String],attribute[A,String] 1,3,left value 3, left attribute 3 1,4,left value 4, left attribute 4 1,5,left value 5, left attribute 5 1,6,left value 6, left attribute 6 id1[I,String],id2[I,String],measure[M,String],attribute[A,String] 1,1,right value 1, right attribute 1 1,2,right value 2, right attribute 2 1,3,right value 3, right attribute 3 1,4,right value 4, right attribute 4

Fold clause

Fold transposes a single data point of the input Dataset into several data points. It adds Identifier dim and measure msr to the resulting Dataset, inserts into the resulting Dataset a data point for each value A in the element list and assigns to the inserted data point dim = A and msr = value of measure A in the input Dataset.

When measure A is null then fold does not create a data point for that measure. Note that in general unfolding and folding are not exactly symmetric operations, i.e. in some cases the fold operation applied to the unfolded Dataset does not recreate exactly the original Dataset (before unfolding).

folded := [population] { fold '0-14', '15-24', '25-64', '65+' to age, percent } unfolded := [folded] { unfold age, percent to "0-14", "15-24" } country[I,String],0-14[M,String],15-24[M,String],25-64[M,String],65+[M,String] France ,18.1%,12.2%,51.0%,18.7% Norway ,18.6%,13.0%,52.0%,16.4% Italy ,14.0%,9.8%,54.4%,21.7% Sweden ,17.3%,12.2%,50.5%,20.0%

Unfold

unfolded := [colors] { unfold color, wavelength to "indigo", "blue", "yellow" } color[I,String],wavelength[M,String] blue, 475nm indigo, 445nm orange, 570nm red, 650nm violet, 400 nm yellow, 510nm

Check operators

The check operators are used to validate data sets. Three check operator variants are available, check with rule sets, check with hierarchical rule sets and check with single boolean rule.

The return value of the check function depend of parameters. When used with the parameter "condition", the resulting dataset will only contain a condition measure of type Boolean indicating if the DataPoint is valid according to the rule set, hierarchical rule set or boolean expression. With the parameter "measures", the resulting dataset contains all the measures of the input dataset.

Check with single rule

folded := check(data.valid <> "na") country[I,String],population[M,String] France , 64M Norway , 5M Italy , na Sweden , 9M

Conditional operators

nvl

The operator nvl replaces null values with a value given as a parameter.

join := [outer left, right] { nvl_result := nvl(right.value, "was null") } id1[I,String],id2[I,String],value[M,String],attribute[A,String] 1,3,left value 3, left attribute 3 1,4,left value 4, left attribute 4 1,5,left value 5, left attribute 5 1,6,left value 6, left attribute 6 id1[I,String],id2[I,String],value[M,String],attribute[A,String] 1,1,right value 1, right attribute 1 1,2,right value 2, right attribute 2 1,3,right value 3, right attribute 3 1,4,right value 4, right attribute 4

if-then-else

    if ds_cond_1 then ds_1 { elseif ds_cond_2 then ds_2 }* else ds_3

The if-then-else operator returns the constant of the first evaluated true condition.

ds_cond_1 is the first Boolean condition, ds_cond_2 is an optional Boolean condition.

ds_1 is a constant returned if ds_cond_1 evaluated true. ds_2 is a constant returned if ds_cond_2 evaluated true. ds_3 is a constant returned if ds_cond_2 not evaluated true.

ds_1, ds_2 and ds_3 must have the same type.

result := [data] { ifThenElseString := if M1 < 2 then "<2" elseif M1 < 3 then "<3" elseif M1 < 4 then "<4" else ">=4", ifThenElseInteger := if M1 < 2 then 0 else 1, ifThenElseFunctions := if M1 is not null then M1 * 2 else nvl(M1, 0) } ID[I,String],M1[M,Long] 1 , 1 2 , 2 3 , 3 4 , 4 5 , null

Boolean operators

Null operators

VTL adopts 3VL (three-value logic); null is not considered a "value", but rather a marker (or placeholder) indicating the absence of value.

p q p OR q p AND q p = q
True True True True True
True False True False False
True Unknown True Unknown Unknown
False True True False False
False False False False True
False Unknown Unknown False Unknown
Unknown True True Unknown Unknown
Unknown False Unknown False Unknown
Unknown Unknown Unknown Unknown Unknown
p NOT p
True False
False True
Unknown Unknown

Null in boolean operators evaluates to false. In order to test whether or not a value is null one can use the postfix operator is null or is not null as well as the functional equivalents isnull() or not(isnull()).

postfixIsNull := [data] { filter value is null } postfixIsNotNull := [data] { filter value is not null } functionalIsNull := [data] { filter isnull(value) } functionalIsNotNull := [data] { filter not(isnull(value)) } country[I,String],value[M,String] Null , null NotNull , value

Hierarchy operator

The hierarchy operator aggregates all measures of a dataset mapping one identifier with a hierarchy.

For instance, consider the following hierarchy:

result := hierarchy(data, data.region, world, true) year[I,String],region[I,String],pop[M,Long] 2000,Belgium,2000L 2000,Denmark,2000L 2000,France,2000L 2000,Hungary,2000L 2000,Iceland,2000L 2000,Italy,2000L 2000,Norway,2000L 2000,Poland,2000L 2000,Portugal,2000L 2000,Romania,2000L 2000,Slovakia,2000L 2000,Spain,2000L 2000,Sweden,2000L 2000,Ukraine,2000L 2002,Belgium,2002L 2002,Denmark,2002L 2002,France,2002L 2002,Hungary,2002L 2002,Iceland,2002L 2002,Italy,2002L 2002,Norway,2002L 2002,Poland,2002L 2002,Portugal,2002L 2002,Romania,2002L 2002,Slovakia,2002L 2002,Spain,2002L 2002,Sweden,2002L 2002,Ukraine,2002L 2004,Belgium,2004L 2004,Denmark,2004L 2004,France,2004L 2004,Hungary,2004L 2004,Iceland,2004L 2004,Italy,2004L 2004,Norway,2004L 2004,Poland,2004L 2004,Portugal,2004L 2004,Romania,2004L 2004,Slovakia,2004L 2004,Spain,2004L 2004,Sweden,2004L 2004,Ukraine,2004L from[I,String],to[I,String],sign[M,String] Western Europe,Europe,+ Eastern Europe,Europe,+ Southern Europe,Europe,+ Northern Europe,Europe,+ Belgium,Western Europe,+ Denmark,Northern Europe,+ France,Western Europe,+ Hungary,Eastern Europe,+ Iceland,Northern Europe,+ Italy,Southern Europe,+ Norway,Northern Europe,+ Poland,Eastern Europe,+ Portugal,Southern Europe,+ Romania,Eastern Europe,+ Slovakia,Eastern Europe,+ Spain,Southern Europe,+ Sweden,Northern Europe,+ Ukraine,Eastern Europe,+

String operators

substr

    String substr(Integer startPosition, Integer length)

The substr operator takes as input startPosition which is the index of the character in the string from which the substring is performed and length which is the number of the characters in the string to be taken starting from startPosition. The operator returns a String value.

result := [data] { sub := substr(M1, 5, 6) } ID[I,String],M1[M,String] 1 , hello world 2 , hello 3 , null

date_from_string

The operator date_from_string converts a string into a date.

join := [left] { b := date_from_string(M1, "YYYY") } ID[I,String],M1[M,String] 1 , 2016 2 , 2017 3 , null

Operators outside the specification

integer_from_string

Note 
    This operator is not part of the VTL 1.1 specification.
    Integer integer_from_string(String value)

The integer_from_string operator takes as input a String value and returns an Integer value.

join := [data] { integerFromString := integer_from_string(M1) } ID[I,String],M1[M,String] 1 , 10 2 , 101 3 , null

float_from_string

Note 
    This operator is not part of the VTL 1.1 specification.
    Float float_from_string(String value)

The float_from_string operator takes as input a String value and returns an Float value. The input value format is described in detail in section 3.10.2 of the Java Language Specification. Additionally a comma (,) is allowed as a decimal point.

join := [data] { floatFromString := float_from_string(M1) } ID[I,String],M1[M,String] 1 , -0.1e-10 2 , -0.1e10 3 , -0.1e+10 4 , 0.1e-10 5 , 0.1e10 6 , 0.1e+10 7 , +0.1e-10 8 , +0.1e10 9 , +0.1e+10 10 , 0.01 11 , -0.001 12 , +0.0001 13 , null

string_from_number

Note 
    This operator is not part of the VTL 1.1 specification.
    String string_from_number(Number value)

The string_from_number operator takes as input a Number value and returns an String value. The exact result of this operator is described in the documentation of java.lang.Double.toString() and java.lang.Long.toString() methods available at Oracle Java Help Center.

join := [data] { stringFromInteger := string_from_number(M1), stringFromFloat := string_from_number(M2) } ID[I,String],M1[M,Long],M2[M,Double] 1 , 10, 10.01 2 , 0, -0.001 3 , null, null