block
The block function is used to render the contents of a block tag more than once.
Example |
|
Output |
|
max
The max function will return the largest of it's numerical arguments.
Example |
{{ max(5, 3) }} |
Output |
|
min
The min function will return the smallest of it's numerical arguments.
Example |
|
Output |
|
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 |
|
range
The range function will return a list containing an arithmetic progression of numbers.
Example |
|
Output |
|
range with step
The range function will return a list containing an arithmetic progression of numbers.
Example |
|
Output |
|
bool
The bool function will return a boolean from parsing a value.
Example |
|
Output |
|
Example 2 |
|
Output |
|
codepoint
The codepoint function will return the character for the specified Unicode code point.
Example |
|
Output |
|
combine lists
The combineLists function will return a merged list of the two arguments.
Example |
|
|
|
double
The double function will return an int for the provided Number argument or attempt to parse a String into a double.
Example |
|
Output |
|
Example 2 |
|
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 |
|
Output |
|
Example 2 |
|
Output |
|
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 |
|
Output |
|
Example 2 |
|
Output |
|
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 |
|
Output |
|
Example 2 |
|
Output |
|
str is equals
The strIsEquals function will return a boolean for whether the provided String arguments are equal. Safely handles null values.
Example |
|
Output |
|
Example 2 |
|
Output |
|
Example 3 |
|
Output |
|
starts with ignore case
The startsWithIgnoreCase function will return a boolean for whether the provided str begins with match while ignoring casing.
Example |
|
Output |
|
ends with ignore case
The endsWithIgnoreCase function will return a boolean for whether the provided str ends with match while ignoring casing.
Example |
|
Output |
|
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.
- QueryGraph.rootTables - If denormalize is true this contains the original table and all directly related tables, otherwise it only contains the original table.
- QueryTable.tables - Contains a list of source tables to generate the query over.
- QueryTable.connection - The associated connection for the set of source tables.
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. - QueryGraph.rootTables - If denormalize is true this contains the original table and all directly related tables, otherwise it only contains the original table.
- QueryTable.tables - Contains a list of source tables to generate the query over.
- QueryTable.connection - The associated connection for the set of source tables.
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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="0b7319e6-a030-42f6-886c-ddc9812a31a7"><ac:plain-text-body><![CDATA[ Avg character count:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="366b1d6e-6e8d-46be-8570-c4a97138ce1a"><ac:plain-text-body><![CDATA[ Avg value:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="c3329445-982d-42d8-9e78-d8576266b0f7"><ac:plain-text-body><![CDATA[ Distinct value count:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="3191905f-d315-4160-92b3-6a3b6d92ed1c"><ac:plain-text-body><![CDATA[ Max character count:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="81e0da36-2c98-4f17-8ee6-d34843251882"><ac:plain-text-body><![CDATA[ Max trimmed length:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="2cadfc28-1ee7-4244-a8d3-251c13e32dbb"><ac:plain-text-body><![CDATA[ Min character count:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- endif -%}
{%- endfor -%}Output
Avg character count: 4
Example 7
{%- for col in table.columns -%}
{%- if col.name == "Region" -%}
{%- set pv = getProfilingValues(col,"Null %",true) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="e087dc0f-a3d8-4483-8d33-6764cd6f6ba5"><ac:plain-text-body><![CDATA[ Percentage Null:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="e36f9276-1450-4ec8-a072-5a7163bedeb9"><ac:plain-text-body><![CDATA[ Null count:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- endif -%}
{%- endfor -%}Output
Null count: 60
Example 9
{%- for col in table.columns -%}
{%- if col.name == "CategoryID" -%}
{%- set pv = getProfilingValues(col,"Numeric range",true) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="c738c9f4-657b-42f5-8552-9b78a2e320f9"><ac:plain-text-body><![CDATA[ Numeric range:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- endif -%}
{%- endfor -%}Output
Numeric range: 7
Example 10
{%- for col in table.columns -%}
{%- if col.name == "Region %" -%}
{%- set pv = getProfilingValues(col,"Populated",true) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="85845bac-b68a-42f9-9f7a-d4cde7d408e7"><ac:plain-text-body><![CDATA[ Percentage populated:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="8bafb190-cffa-4862-a9c1-7ffc227f90db"><ac:plain-text-body><![CDATA[ Populated count:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- endif -%}
{%- endfor -%}Output
Populated count: 31
Example 12
{%- for col in table.columns -%}
{%- if col.name == "CategoryID" -%}
{%- set pv = getProfilingValues(col,"Sum value",true) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="598cfe16-d438-473a-9399-6bb4dc5e2a40"><ac:plain-text-body><![CDATA[ Sum value:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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: 2Example 14
{%- for col in table.columns -%}
{%- if col.name == "CategoryID" -%}
{%- set pv = getProfilingValues(col,"Min value",true) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="9fb7e549-658b-4fdb-9261-a3d9314fda29"><ac:plain-text-body><![CDATA[ Min value:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- endif -%}
{%- endfor -%}Output
Min value: 1
Example 15
{%- for col in table.columns -%}
{%- if col.name == "CategoryID" -%}
{%- set pv = getProfilingValues(col,"Max value",true) -%}
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="5702a3bb-4a7e-42a9-b220-155a57ddb7a2"><ac:plain-text-body><![CDATA[ Max value:pv[0].snd
{%br%}
]]></ac:plain-text-body></ac:structured-macro>
{%- 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
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="2799eab9-8486-4913-9554-f7039108aad4"><ac:plain-text-body><![CDATA[
{{ quote("SELECT * FROM `TUTORIAL`.`CUSTOMER`;", "[", "]") }}
]]></ac:plain-text-body></ac:structured-macro>
Output
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="ea1c2f8c-e5eb-4247-bd3a-414a0cf9aaf5"><ac:plain-text-body><![CDATA[
SELECT * FROM [TUTORIAL].[CUSTOMER];
]]></ac:plain-text-body></ac:structured-macro>
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