Function List

Mipler Flow supports advanced expressions for blending, transforming, and cleaning data in various ways. Functions can be used to create different expressions for new columns, filters, or special transformations.

To create new columns or modify existing ones, you can use a combination of functions in the Create Calculation step. Additionally, expressions can be used in the Filter step to apply advanced filtering logic.

When using functions, don't forget to add backticks for columns column1 and quotes for strings "text".

Examples of different expressions for the Create Calculation step:

json_path(`billing_address`, "city")

year(`created_at`)

case_when(`discount_amount` > 0, (`discount_amount` / `grand_amount`) * 100, NULL)

Examples for the Filter step:

trim(`body`) = ""

and(year(`created_at`) > 2020, year(`created_at`) <= 2022)

Here is a list of key functions available in each category:

Name	Type	Description
as_timestamp	Date and Time	Converts a string value to a date-time value (timestamp type).
month	Date and Time	Returns the month of the year a specific date falls in, in numeric format.
year	Date and Time	Returns the year specified by a given date.
and	Logical	Returns `TRUE` if all of the provided arguments are logically `TRUE`, and `FALSE` if any of the provided arguments are logically `FALSE`.
case_when	Logical	Returns one value if a logical expression (condition) is `TRUE` and another if it is `FALSE`.
is_empty	Logical	Checks whether the referenced column or expression is empty or NULL.
is_null	Logical	Checks whether the referenced column or expression is NULL.
not	Logical	Returns the opposite of a logical value - `not(TRUE)` returns `FALSE`; `not(FALSE)` returns `TRUE`.
or	Logical	Returns `TRUE` if any of the provided arguments are logically `TRUE`, and `FALSE` if all of the provided arguments are logically `FALSE`.
concat	Summarization	The `concat` function combines strings into a single string. This function is useful for merging text data or creating composite keys.
count	Summarization	Counts the number of distinct values by given group.
cumsum	Summarization	The `cumsum` function calculates the cumulative sum of a numeric column. This function is useful for performing running totals, which are common in financial calculations, cumulative performance analysis, and tracking progressive data trends.
max	Summarization	Finds the maximum value in a column.
min	Summarization	Finds the minimum value in a column.
row_number	Summarization	The `row_number` function assigns a sequential integer to each row in the data set. This function is typically used to generate a unique identifier for each row, which can be useful for ranking, pagination, and identifying specific rows in ordered datasets.
rows	Summarization	The `rows` function counts the total number of rows in a data set.
sum	Summarization	Calculates the total sum of numeric values in a column.
arrange	Table manipulation	Arranges rows of a table based on specified order criteria.
asc	Table manipulation	Specifies ascending order for a column.
desc	Table manipulation	Specifies descending order for a column.
filter	Table manipulation	Returns a table that has been filtered based on a specified condition.
left_join	Table manipulation	Performs a left join on two tables based on specified criteria.
mutate	Table manipulation	Adds new columns or modifies existing columns in a table.
select	Table manipulation	Selects specific columns from a table.
summarize	Table manipulation	Summarizes a table by aggregating data.
top_n	Table manipulation	Returns the top N rows of the table based on specified criteria.
as_string	Text	The `as_string` function converts various data types into a string format. This function is particularly useful when you need to ensure that data is in a consistent, readable format for display, concatenation, or further text-based operations.
json_path	Text	Allows navigation and retrieval of specific data from JSON objects or arrays based on a specified path or query.
regex_extract	Text	The `regex_extract` function takes an input string and a regular expression (regex) pattern and extracts all the substrings that match the pattern. It returns a list of these matching substrings. This function is useful for parsing text and extracting specific data based on patterns, such as extracting email addresses, phone numbers, dates, or any other patterns from a given text.
str_contains	Text	Returns `TRUE` if a column or expression contains the specified pattern.
trim	Text	The `trim` function removes leading and trailing whitespace and new line characters from a string. This is useful for cleaning and standardizing text data, ensuring there are no extra spaces or lines at the beginning or end of the strings.