VTL Documentation
User Manual
Foreword
Introduction
Structure of the document
General characteristics of the VTL
User orientation
Integrated approach
Active role for processing
Independence of IT implementation
Extensibility, customizability
Language effectiveness
Evolution of VTL 2.0 in respect to VTL 1.0
The Information Model
Structural artefacts and reusable rules
The core language and the standard library
The user defined operators
The VTL Definition Language
The functional paradigm
The operators
Changes for version 2.2
Changes for version 2.1
VTL Information Model
Introduction
Generic Model for Data and their structures
Data model diagram
Explanation of the Diagram
Functional Integrity
Examples
The data artefacts
Generic Model for Variables and Value Domains
Variable and Value Domain model diagram
Explanation of the Diagram
Relations and operations between Code Items
Conditioned Code Item Relations
The historical changes
The Variables and Value Domains artefacts
Generic Model for Transformations
Transformations model diagram
Explanation of the diagram
Examples
Functional paradigm
Transformation Consistency
VTL Data types
Data Types overview
Data Types model diagram
Explanation of the diagram
General conventions for describing the types
Scalar Types
Basic Scalar Types
Value Domain Scalar Types
Set Scalar Types
External representations and literals used in the VTL Manuals
Conventions for describing the scalar types
Compound Data Types
Component Types
Data Set Types
Product Types
Operator Types
Ruleset Types
Universal Set Types
Universal List Types
VTL Transformations
The Expression
The Assignment
The Result
The names
The artefact names
The environment name
The connection to the persistent storage
VTL Operators
The categories of VTL operators
The input parameters
The invocation of VTL operators
Level of operation
The Operators’ behaviour
The Join operators
Other operators: default behaviour on Identifiers, Measures and Attributes
The Identifier Components and the Data Points matching
The operations on the Measure Components
Operators which change the basic scalar type
Boolean operators
Set operators
Behaviour for Missing Data
Behaviour for Attribute Components
The Attribute propagation rule
Properties of the Attribute propagation algorithm
Governance, other requirements and future work
The governance of the extensions
Relations with the GSIM Information Model
Data Sets and Data Structures
Value Domains
Transformation model and Business Process Model
Annex 1 – EBNF
Properties of VTL grammar
Reference Manual
Foreword
Introduction
Overwiew of the language and conventions
Introduction
Conventions for writing VTL Transformations
Typographical conventions
Abbreviations for the names of the artefacts
Conventions for describing the operators’ syntax
Description of data types of operands and result
VTL-ML Operators
VTL-ML - Evaluation order of the Operators
Description of VTL Operators
VTL-DL - Rulesets
define datapoint ruleset
Semantics
Syntax
Syntax description
Parameters
Constraints
Semantic specification
Examples
define hierarchical ruleset
Semantics
Syntax
Syntax description
Input parameters type
Constraints
Semantic specification
Examples
VTL-DL – User Defined Operators
define operator
Syntax
Syntax description
Input parameters type
Constraints
Semantic specification
Examples
Data type syntax
VTL-ML - Typical behaviours of the ML Operators
Typical behaviour of most ML Operators
Operators applicable on one Scalar Value or Data Set or Data Set Component
Operators applicable on two Scalar Values or Data Sets or Data Set Components
Operators applicable on more than two Scalar Values or Data Set Components
Behaviour of Boolean operators
Behaviour of Set operators
Behaviour of Time operators
Operators changing the data type
Type Conversion and Formatting Mask
The Numbers Formatting Mask
The Time Formatting Mask
Attribute propagation
Operators
VTL-ML - General Purpose Operators
Parentheses:
()
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Persistent assignment:
<-
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Non-persistent assignment:
:=`
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Membership:
#
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
User-defined operator call
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Evaluation of an external routine:
eval
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Type conversion:
cast
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Join operators
Inner Join
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Left Join
Syntax
Input parameters
Examples of valid syntaxes
Input parameters type
Result type
Additional Constraints
Behaviour
Full Join
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Cross Join
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - String Operators
String concatenation:
\
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Whitespace removal:
trim
,
rtrim
,
ltrim
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Character case conversion:
upper/lower
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Sub-string extraction:
substr
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
String pattern replacement:
replace
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
String pattern location:
instr
Syntax
Input parameters
Examplesof valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
String length:
length
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Numeric Operators
Unary Plus:
+
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Unary Minus:
-
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Addition:
+
Syntax
Input parameters
examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Subtraction:
-
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Multiplication:
*
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Division:
/
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Modulo:
mod
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Rounding:
round
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Truncation:
trunc
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Ceiling:
ceil
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Floor:
floor
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Absolute value:
abs
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Exponential:
exp
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Natural logarithm:
ln
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Power:
power
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Logarithm:
log
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Square root:
sqrt
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Random:
random
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Comparison Operators
Equal to:
=
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Not equal to:
<>
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Greater than:
> >=
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Less than
< <=
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Between
between
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Element of
in / not_in
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Match characters:
match_characters
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Is null:
isnull
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Exists in:
exists_in
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Boolean Operators
Logical conjunction:
and
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Logical disjunction:
or
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Exclusive disjunction:
xor
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Logical negation:
not
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Time Operators
Period indicator:
period_indicator
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Fill time series:
fill_time_series
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Flow to stock:
flow_to_stock
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Stock to flow:
stock_to_flow
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Time shift:
timeshift
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Time aggregation:
time_agg
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Actual time:
current_date
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Days between two dates:
datediff
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Add a time unit to a dete:
dateadd
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Extract time period from a date:
getyear, getmonth, dayofmonth, dayofyear
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Number of days to duration:
daytoyear, daytomonth
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Duration to number of days:
yeartoday, monthtoday
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Set Operators
Union:
union
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Intersection:
interesect
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Set difference:
setdiff
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Symmetric difference:
symdiff
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Hierarchical aggregation
Hierarchical roll-up:
hierarchy
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Aggregate and Analytic operators
Aggregate invocation
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Analytic invocation
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Counting the number of data points:
count
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Minimun value:
min
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Maximum value:
max
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Median value:
median
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Sum:
sum
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Average value:
avg
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Population standard deviation:
stddev_pop
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Sample standard deviation:
stddev_samp
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Population variance:
var_pop
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Sample variance:
var_samp
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
First value:
first_value
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Last value:
last_value
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Lag:
lag
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Lead:
lead
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Rank:
rank
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Ratio to report:
ratio_to_report
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Data Validation Operators
Check datapoint:
check_datapoint
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Check hierarchy:
check_hierarchy
Syntax
Input parameters
Examples of valid syntaxes
Input parameters type
Result type
Additional Constraints
Behaviour
Check :
check
Syntax
Input parameters
Examples of valid syntaxes
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Conditional Operators
if-then-else:
if
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Case:
case
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Nvl:
nvl
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL-ML - Clause Operators
Filtering Data Points:
filter
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Calculation of a Component:
calc
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Aggregation:
aggr
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Maintaining Components:
keep
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Removal of Components:
drop
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Change of Component name:
rename
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Pivoting:
pivot
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Unpivoting:
unpivot
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Subspace:
sub
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
VTL Documentation
Documentation for VTL v2.2
Edit on GitHub
Documentation for VTL v2.2
Propagation test (backport validation)
update develop
fix v2.1 and propagate to v2.2
User Manual
Reference Manual
PDF Version