block

The block function is used to render the contents of a block tag more than once.

Example

{% block "testBlock" %}Some content
{% endblock %}
{{ block("testBlock") }}

Output

Some contentSome content

max

The max function will return the largest of it's numerical arguments.

Example

{{ max(5, 3) }}

Output

5

min

The min function will return the smallest of it's numerical arguments.

Example

{{ min(5, 3) }}

Output

3

parent

The parent function is used inside of a block to render the content that the parent template would have rendered inside of the block had the current template not overriden it.

Template

{% block "${blockName}" %}
{{ parent() }}
{% endblock %}

range

The range function will return a list containing an arithmetic progression of numbers.

Example

{{ range(3, 6) }}

Output

[3, 4, 5, 6]

range with step

The range function will return a list containing an arithmetic progression of numbers.

Example

{{ range(2, 10, 2) }}

Output

[2, 4, 6, 8, 10]

bool

The bool function will return a boolean from parsing a value.

Example

{{ bool("T") }}

Output

true


Example 2

{{ bool(0) }}

Output

false

codepoint

The codepoint function will return the character for the specified Unicode code point.

Example

{{ codepoint(34) }}

Output

"

combine lists

The combineLists function will return a merged list of the two arguments.

Example

{% set listA = [ "a", "b", "c" ] %}
{% set listB = [ "d", "c", "e" ] %}
{{ combineLists(false, listA, listB) }}
{{ combineLists(true, listA, listB) }}

Output

[a, b, c, d, c, e][a, b, c, d, e]

double

The double function will return an int for the provided Number argument or attempt to parse a String into a double.

Example

{{ double(3.145) }}

Output

3.145


Example 2

{{ double("3.145") }}

Output

3.145

int

The int function will return an int for the provided Number argument or attempt to parse a String into an int.

Example

{{ double("3.145") }}

Output

3


Example 2

{{ int("3.145") }}

Output

3

replace start

The replaceStartfunction will return a String where str will be checked if it starts with match and if so be replaced with replace.

Example

{{ replaceStart("prefix", "pre", "suf", false) }}

Output

suffix


Example 2

{{replaceStart("Prefix", "pre", "suf", false) }}

Output

Prefix

replace end

The replaceEnd function will return a String where str will be checked if it ends with match and if so be replaced with replace.

Example

{{ replaceEnd("completion", "ion", "ed", false) }}

Output

completed


Example 2

{{replaceEnd("Completion", "ION", "ed", false) }}

Output

Completion

str is equals

The strIsEquals function will return a boolean for whether the provided String arguments are equal. Safely handles null values.

Example

{{ strIsEquals("a", "A", false) }}

Output

true


Example 2

{{strIsEquals("a", "A", false) }}

Output

false


Example 3

{{ strIsEquals("a", null) }}

Output

false

starts with ignore case

The startsWithIgnoreCase function will return a boolean for whether the provided str begins with match while ignoring casing.

Example

{{ startsWithIgnoreCase("SuperMan", "super") }}

Output

true

ends with ignore case

The endsWithIgnoreCase function will return a boolean for whether the provided str ends with match while ignoring casing.

Example

{{ endsWithIgnoreCase("SuperMan", "man") }}

Output

true

3D Specific Functions

closestConnection(Table table or Column column)

The closestConnection function will return a Connection that is the closest when traversing the source mappings.

Template

{% set ${graphs} = closestConnection(table) %}

createSourceGraph(Table table, boolean style, boolean denormalize)

The createSourceGraph function will return a list of QueryGraphs, one for each source connection, containing the tables for a query.
The style flag is set to true for calculating a data query or false for a data warehouse query.
The denormalize flag determines whether to calculate a query with any related tables to the provided table.

Template

{% set ${graphs} = createSourceGraph(table, ${style}, ${denormalize}) %}

createSourceGraphBySet(Table table, String sourceSetName, boolean style, boolean denormalize)

The createSourceGraphBySet function will return a list of QueryGraphs, one for each source connection, containing the tables for a query by the given source set.
The style flag is set to true for calculating a data query or false for a data warehouse query.
The denormalize flag determines whether to calculate a query with any related tables to the provided table.

Template

{% set ${graphs} = createSourceGraphBySet(table, ${sourceSet}, ${style}, ${denormalize}) %}

getProfilingValues(Table/Column/Relation obj, String metric, boolean casing)

The getProfilingValues function will return a list of all the values for the specified profiling metric name on a given object.

Example

{%- for col in table.columns -%}
    {%- if col.name == "City" -%}
        {%- set pv = getProfilingValues(col,"Avg character count",true) -%}
        Avg character count: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Avg character count: 8


Example 2

{%- for col in table.columns -%}
    {%- if col.name == "CategoryID" -%}
        {%- set pv=getProfilingValues(col,"AVG Value",false) -%}
        Avg value: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Avg value: 4.5


Example 3

{%- for col in table.columns -%}
    {%- if col.name == "City" -%}
        {%- set pv = getProfilingValues(col,"Distinct value count",true)
%}
        Distinct value count: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Distinct value count: 69


Example 4

{%- for col in table.columns -%}
    {%- if col.name == "City" -%}
        {%- set pv = getProfilingValues(col,"Max character count",true) -%}
        Max character count: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Max character count: 15


Example 5

{%- for col in table.columns -%}
    {%- if col.name == "City" -%}
        {%- set pv = getProfilingValues(col,"Max trimmed length",true) -%}
        Max trimmed length: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Max trimmed length: 15


Example 6

{%- for col in table.columns -%}
    {%- if col.name == "City" -%}
        {%- set pv = getProfilingValues(col,"Min character count",true) -%}
        Min character count: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Avg character count: 4


Example 7

{%- for col in table.columns -%}
    {%- if col.name == "Region" -%}
        {%- set pv = getProfilingValues(col,"Null %",true) -%}
        Percentage Null: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Percentage Null: 65.93%


Example 8

{%- for col in table.columns -%}
    {%- if col.name == "Region" -%}
        {%- set pv = getProfilingValues(col,"Null count",true) -%}
        Null count: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Null count: 60


Example 9

{%- for col in table.columns -%}
    {%- if col.name == "CategoryID" -%}
        {%- set pv = getProfilingValues(col,"Numeric range",true) -%}
        Numeric range: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Numeric range: 7


Example 10

{%- for col in table.columns -%}
    {%- if col.name == "Region %" -%}
        {%- set pv = getProfilingValues(col,"Populated",true) -%}
        Percentage populated: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Percentage Populated: 34.07%


Example 11

{%- for col in table.columns -%}
    {%- if col.name == "Region" -%}
        {%- set pv = getProfilingValues(col,"Populated count",true) -%}
        Populated count: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Populated count: 31


Example 12

{%- for col in table.columns -%}
    {%- if col.name == "CategoryID" -%}
        {%- set pv = getProfilingValues(col,"Sum value",true) -%}
        Sum value: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Sum value: 36.0


Example 13

{%- for col in table.columns -%}

    {%- if col.name == "City" -%}

        {%- set pv = getProfilingValues(col,"Top 10 most frequent values",true) -%}

        Top 10 most frequent values:{%br%}

        {%- for v in pv -%}

            {{loop.index + 1}}- {{v.fst}}: {{v.snd}}{%br%}

        {%- endfor -%}

    {%- endif -%}

{%- endfor -%}

Output

Top 10 most frequent values
1- London: 6
2- México D.F.: 5
3- Sao Paulo: 4
4- Rio de Janeiro: 3
5- Madrid: 3
6- Buenos Aires: 3
7- Paris: 2
8- Portland: 2
9- Nantes: 2
10- Lisboa: 2


Example 14

{%- for col in table.columns -%}
    {%- if col.name == "CategoryID" -%}
        {%- set pv = getProfilingValues(col,"Min value",true) -%}
        Min value: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Min value: 1


Example 15

{%- for col in table.columns -%}
    {%- if col.name == "CategoryID" -%}
        {%- set pv = getProfilingValues(col,"Max value",true) -%}
        Max value: {{pv[0].snd}}{%br%}
    {%- endif -%}
{%- endfor -%}

Output

Max value: 8

mapDuplicateAlias(List<Table> tables, List<QueryTable> queryTables)

The mapDuplicateAlias function returns a mapping from source table aliases to root tables in the tables list with multiple aliases in the queryTables list.

Template

{% set ${mapping} = mapDuplicateAlias( ${tables}, %{queryTables}) %}

parseQuery(String query)

The parseQuery function will parse a query String into a QueryDefinition object.

Template

{% set ${parsed} = parseQuery( ${query}) %}

quote(String query, String openQuote, String closeQuote)

The quote function convert any '`'s to the provided open and closing quotes.

Template

{{ quote("SELECT * FROM `TUTORIAL`.`CUSTOMER`;", "[", "]") }}

Output

SELECT * FROM [TUTORIAL].[CUSTOMER];

uniqueTableName(Table table, String tableName, String schemaName)

The uniqueTableName function will return a String for a unique table name based on the provided names.

Example

{#- table is named TUTORIAL.CUSTOMER. And TUTORIAL.PRODUCT exists -#}

{{- uniqueTableName(table, "CUSTOMER", "TUTORIAL") -}}{%- br -%}
{{- uniqueTableName(table, "PRODUCT", "TUTORIAL") -}}

Output

CUSTOMER
PRODUCT_1