Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

The cloud-based product Trifacta Free is available now! Register for your free account.

 

Contents:


Extracts the set of values from a column into an array stored in a new column. This function is typically part of an aggregation. 

Tip: To generate unique values for the list, apply the ARRAYUNIQUE function in the next step after this one. See ARRAYUNIQUE Function.

 

Input column can be of any type.

  • By default, the list is limited to 1000 values. To change the maximum number of values, specify a value for the limit parameter.
  • This function is intended to be used as part of an aggregation to return the distinct set of values by group. See Pivot Transform.

For a version of this function computed over a rolling window of rows, see ROLLINGLIST Function.

Basic Usage

pivot value:LIST(Name, 500) group:Month limit:1

Output: Generates a two-column table containing the values for Month and an array of all values (up to a count of 500) from the Name column for each Month value. The limit parameter defines the maximum number of output columns.

Syntax and Arguments

pivot value:LIST(function_col_ref, [limit_int]) [group:group_col_ref] [limit:limit_count]

ArgumentRequired?Data TypeDescription
function_col_refYstringName of column to which to apply the function
limit_intNinteger (positive)Maximum number of values to extract into the list array. From 1 to 1000.

For more information on the group and limit parameters, see Pivot Transform.

For more information on syntax standards, see Language Documentation Syntax Notes.

function_col_ref

Name of the column from which to extract the list of values based on the grouping.

  • Literal values are not supported as inputs.
  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference)myValues

limit_int

Non-negative integer that defines the maximum number of values to extract into the list array. 

NOTE: If specified, this value must between 1 and 1000, inclusive.

NOTE: Do not use the limiting argument in a LIST function call on a flat aggregate, in which all values in a column have been inserted into a single cell. In this case, you might be able to use the limit argument if you also specify a group parameter. Misuse of the LIST function can cause the application to crash.

Usage Notes:

 

Required?Data TypeExample Value
NoInteger50


Examples

Tip: For additional examples, see Common Tasks.

Example - Colors sold this month

Unable to render {include} The included page could not be found.

 

  • No labels

This page has no comments.