Understanding Expressions and Functions in Transforms

1: Summary

Hub Loader transforms support an extensive set of expressions and functions that allow you to:

• Perform math and logic operations

• Filter data conditionally

• Work with dimensions and compound variables

• Aggregate or map values

This guide lists all supported expression syntax and built-in functions with usage examples.

2: Expression Basics

Expressions are used in:

• Variable definitions (value:)

• Filter expressions (filter expression { value: ... })

• Update actions (action update { value: ... })

2.1: Supported Operators

Type	Examples
Math	+, -, *, /
Comparison	<, <=, =, >=, !=
Boolean logic	AND, OR, not()

Expressions work with both single values and vectors (lists).

When operating on two vectors, they must be the same size.

3: Common Functions

iif(condition, trueExpr, falseExpr)

Conditional logic — returns trueExpr or falseExpr based on condition.

iif(score(:q1) > 3, "High", "Low")

not(expression)

Returns the opposite of a boolean value.

not(:isActive)

recode(value, @recodingEntity)

Maps a value using a defined recoding ranges or recoding values.

recode(:q1, @mappingWave1)

list(...)

Creates a list of values or vPaths.

list(:q1, :q2, :q3)

4: Aggregation Functions

sum(list)

Returns the sum of numeric values.

sum(list(1, 2, 3)) → 6

avg(list)

Returns the average. Ignores nulls.

avg(list(2, 4, null)) → 3

Sublevel usage:

avg(.loopLevel:, score(:q1))

Calculates the average by evaluating the expression in the second argument for each row in the “loopLevel” level.

any(subLevel, expression)

Returns true if any record in the loop level passes the expression.

any(.loopLevel:, score(:q1) < 3)

5: Dimension-Specific Functions

Only usable in table dimension transforms.

dimensionAvg([variableNamefield])

Returns average of dimension items.

If field is given, we will instead calculate the dimension avg from a compound numeric variable of that name, using the variable name as the field

dimensionAvg()
dimensionAvg("scaleditems")

dimensionItems()

Returns a list of vPaths used in the current dimension.

Replaces deprecated dimensionQuestions and dimensionItems functions.

dimensionValues()

Returns all values from items in the current dimension.

6: Variable Iteration Functions

Only usable in table unPivot.

variableValue([field])

Gets the value of the current variable in the unPivot loop.

variableValue()
variableValue("rating")

7: Miscellaneous

score(vPath | list)

Returns score for singleChoice/rating variables.

score(:q1)
score(list(:q1, :q2)) → [1, 2]

substr(string, start, length)

Returns a substring.

substr("abcde", 2, 3) → "bcd"

isNull(x)

Returns true if x is null.

isNull(:q1)

isNull(x, fallback)

Returns x unless it’s null, then returns fallback.

isNull(:score, 0)

8: Date Functions

Function	Description
year(date)	Extracts the year
quarter(date)	Extracts the quarter (1–4)
month(date)	Extracts the month (1–12)
day(date)	Extracts the day of month

9: Deprecated Functions

Deprecated	Use Instead
dimensionItems	dimensionQuestions (merged)
vscore	score
viif	iif

10: Best Practices

• Always test complex expressions with test data.

• Use iif() and isNull() together to handle missing values gracefully.

• Use score() only with variables of type singleChoice or rating.

• When aggregating in loops or dimensions, avoid mixing types (e.g., null, string, number).