Savant

Discover the array of tools and functions at your disposal within Savant.

Tools and Functions

Tools Overview

Verify that the input data matches the expected output, guaranteeing the correct column names and data types.

Adapter Tool ⭐️

Combine multiple datasets by stacking rows to consolidate similar data into one output.

Blend Tool ⭐️

Combine datasets based on matching fields, with the flexibility to handle different join types.

Dedupe Tool ⭐️

Cross joins between datasets with zero configuration, making it effortless to combine every row from one dataset with every row from another.

Explode Tool ⭐

Use the Filter Tool to isolate records based on conditions in your dataset, utilizing either the Builder or Expression options.

Filter Tool ⭐️

Use the Format Tool to modify and format your dataset by dynamically changing field names, data types, and other schema properties.

Format Tool ⭐️

Utilize the power of Generative AI in your data preparation process.

Gen AI Tool ⭐️

Make API calls and retrieve data from external APIs.

Generic API Tool ⭐️

Perform joins similar to Excel's VLOOKUP; merge data from different tables based on specific conditions.

Join Tool ⭐️

Parse and transform JSON data into a structured format for analysis.

JSON Tool ⭐️

Summarize and transform your data by grouping and aggregating values with the Pivot Tool.

Pivot Tool ⭐️

The Recursion tool recursively calculates data across levels in a parent-child tree.

Recursion Tool ⭐️

The Sample tool extracts specific data portions: first N rows, every Nth row, or a percentage.

Sample Tool ⭐️

Use the Split Tool to divide text fields into separate columns or rows based on a specified delimiter.

Split Tool ⭐️

Stack Tool ⭐️

Find missing records & non-matches between tables.

Subtract Tool ⭐️

Summarize tool groups, sorts, and aggregates data with functions like SUM, COUNT, AVG. Configure windows for detailed analysis.

Summarize Tool ⭐️

Perform complex aggregations on time series data without the need for extensive data parsing or extraction.

Time Series Tool ⭐️

The Transform tool is used to format, arrange and calculate data, just like you would in a spreadsheet.

Transform Tool ⭐️

Convert wide datasets into a long format.

Unpivot Tool ⭐️

Learn about the Savant tools and become and analytics master

Functions

List of operators for use in Edit and Filter expressions.

Operators

Build your perfect analysis with Savant's powerful and intuitive functions

Functions Overview

Creates a date based on the provided year, month, and day values

DATE

Adds a specified offset of time (e.g., seconds, minutes, hours) to a given date or datetime

DATE_ADD

Calculates the difference in days, weeks, months, quarters, or years between two dates

DATE_DIFF

Creates a datetime based on the provided year, month, day, hour, minute, and second values

DATETIME

Retrieves the day of the month from a given date or datetime

Determines the day of the week for a given date or datetime, where Monday is 1, Tuesday is 2, and so on

DAY_OF_WEEK

Calculates the day of the year for a given date or datetime

DAY_OF_YEAR

Calculates the number of days in the month for a given date or datetime

DAYS_IN_MONTH

Calculates the number of days in a year for a given date or datetime, considering leap years

DAYS_IN_YEAR

Extracts the hour component from a given datetime

HOUR

Adds a specified number of days, weeks, months, quarters, or years to a date

INTERVAL (Date)

Adds a specified number of seconds, minutes, or hours to a datetime

INTERVAL (DateTime)

Extracts the minute component from a given datetime

MINUTE

Retrieves the month component from a given date

MONTH

Retrieves the quarter of the year from a given date or datetime

QUARTER

Extracts the second component from a given datetime

SECOND

Returns a standard time period (e.g., day, week, month, quarter, year) for a given date or datetime field

TIME_PERIOD

Calculates the number of time periods between a given date and the current period

TIME_PERIOD_OFFSET

TODAY

Determines the week number of the year for a given date or datetime

WEEK/WEEK_OF_YEAR

Retrieves the year component from a given date or datetime

YEAR

Date and DateTime

Converts a value to a boolean (true/false) format

TO_BOOLEAN

TO_DATE

TO_DATETIME

TO_INTEGER

TO_NUMBER

Converts a value, whether it's a datetime or any other data type, to a text format

TO_TEXT

Formatting

Evaluates conditions and returns a value based on the first true condition

CASE ... WHEN ...

Checks if a value exists within a specified set of values

Keywords

Returns one value if a condition is true and another if false.

Returns a value corresponding to the first true condition

Logical

Returns the absolute value of a number, ignoring its sign (+/-)

Rounds a number up to the nearest integer

CEILING

Rounds a number down to the nearest integer

FLOOR

Returns the greatest value among the given columns (i.e., arguments)

GREATEST

Returns the smallest value among the given columns (i.e., arguments)

LEAST

Calculates the logarithm of a number to a specified base

Returns the remainder of a division operation

Raises a number to the power of another number

POWER

Generates a random decimal value between 0 and 1

RANDOM

Rounds a number to a specified number of decimal places

ROUND

ROUNDDOWN

ROUNDUP

Math

Calculates the average value of a specified numeric field across a range or partition

Combines unique text values from multiple rows in a specified field into a single text value

CONCAT

CONCAT DISTINCT

Counts the number of rows within a specified range or partition

COUNT

Counts the number of unique values in a specified field within a range or partition

COUNT DISTINCT

Calculates the cumulative sum of a specified numeric field across a range or partition

CUMULATIVE SUM

Populates missing values in a specified field using a defined strategy, such as forward fill or backward fill

FILL

Get the first value of a specified field within a range or partition

FIRST

Get the value of a specified field from a previous row in the dataset

Get the last value of a specified field within a range or partition

LAST

Get the value of a specified field from a subsequent row in the dataset.

LEAD

Get the maximum value of a specified field within a range or partition

Get the median value of a specified numeric field within a range or partition

MEDIAN

Get the minimum value of a specified field within a range or partition

Assigns a bucket number to each row in a partition

NTILE

Assigns a rank to each row within a partition of the dataset, with ties receiving the same rank

RANK

ROW NUMBER

Calculates the standard deviation of a specified numeric field within a range or partition

STDDEV

Calculates the total sum of a specified numeric field within a range or partition

Calculates the variance of a specified numeric field within a range or partition.

Multi-Row Aggregation & Windowing

Returns the first non-null value from a list of options.

COALESCE

Concatenates multiple text strings into a single string

Checks if text contains specified subtext.

CONTAINS

Returns the first non-empty value from a list of options.

FIRST_NOT_EMPTY

Computes the hash value of a text using a specified algorithm (MD5 or SHA-1)

HASH

Escapes HTML special characters in a text string

HTML_ESCAPE

IS_EMPTY

JSON_FIELD

Sets a value for a specific field in JSON data

JSON_SET

Encodes a JSON Web Token for usage with APIs

JWT_ENCODE

Retrieves the leftmost characters from a text string.

LEFT_PART

Computes the length (number of characters) of a text string

LENGTH

Measures the edit distance (Levenshtein distance) between two text strings

LEVENSHTEIN

Converts text to all lowercase characters

LOWER

Pads the left-side of text with a specified character to ensure a minimum text length

PAD_LEFT

Pads the right-side of text with a specified character to ensure a minimum text length

PAD_RIGHT

Capitalizes the first letter of each word in a text string

PROPER

Extracts text that matches a regular expression pattern

REGEX_EXTRACT

Checks if a text string matches a specified regular expression pattern

REGEX_MATCH

Replaces text that matches a regular expression pattern with a specified replacement

REGEX_REPLACE

Returns the index of the first character of the first matching substring based on a regular expression pattern