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.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
Examples
Persistent assignment:
<-
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Non-persistent assignment:
:=`
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Membership:
#
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
User-defined operator call
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Type conversion:
cast
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
VTL-ML - Join operators
Join:
inner_join
,
left_join
,
full_join
,
cross_join
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
VTL-ML - String Operators
String concatenation:
\
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Whitespace removal:
trim
,
rtrim
,
ltrim
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Character case conversion:
upper/lower
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Sub-string extraction:
substr
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
String pattern replacement:
replace
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
String pattern location:
instr
Syntax
Input parameters
Examplesof valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
String length:
length
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Unary Minus:
-
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Addition:
+
Syntax
Input parameters
examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Subtraction:
-
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Multiplication:
*
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Division:
/
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Modulo:
mod
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Rounding:
round
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Truncation:
trunc
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Ceiling:
ceil
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Floor:
floor
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Absolute value:
abs
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Exponential:
exp
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Natural logarithm:
ln
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Power:
power
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Logarithm:
log
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Square root:
sqrt
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Random:
random
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Not equal to:
<>
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Greater than:
> >=
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Less than
< <=
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Between
between
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Element of
in / not_in
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Match characters:
match_characters
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Is null:
isnull
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Exists in:
exists_in
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Logical disjunction:
or
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Exclusive disjunction:
xor
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Logical negation:
not
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
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
Examples
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
Examples
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
Examples
Time shift:
timeshift
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Time aggregation:
time_agg
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Actual time:
current_date
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Days between two dates:
datediff
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
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
Examples
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
Examples
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
Examples
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
Examples
Intersection:
interesect
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Set difference:
setdiff
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Symmetric difference:
symdiff
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
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
Examples
Analytic invocation
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Counting the number of data points:
count
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Minimun value:
min
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Maximum value:
max
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Median value:
median
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Sum:
sum
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Average value:
avg
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Population standard deviation:
stddev_pop
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Sample standard deviation:
stddev_samp
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Population variance:
var_pop
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Sample variance:
var_samp
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
First value:
first_value
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Last value:
last_value
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Lag:
lag
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Lead:
lead
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Rank:
rank
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Ratio to report:
ratio_to_report
Syntax
Input parameters
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Check hierarchy:
check_hierarchy
Syntax
Input parameters
Examples of valid syntaxes
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Check :
check
Syntax
Input parameters
Examples of valid syntaxes
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Case:
case
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Nvl:
nvl
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
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
Examples
Calculation of a Component:
calc
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behavior
Examples
Aggregation:
aggr
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Maintaining Components:
keep
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Removal of Components:
drop
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Change of Component name:
rename
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Pivoting:
pivot
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Unpivoting:
unpivot
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
Subspace:
sub
Syntax
Input parameters
Examples of valid syntaxes
Semantics for scalar operations
Input parameters type
Result type
Additional Constraints
Behaviour
Examples
VTL Documentation
Documentation for VTL v2.1
Edit on GitHub
Documentation for VTL v2.1
fix 2.1 only
fix v2.1 and propagate to v2.2
New fix v2.1 and propagate to v2.2
User Manual
Reference Manual
PDF Version