ANY Function

Extracts a non-null and non-missing value from a specified column. If all values are missing or null, the function returns a null value.

This function is intended to be used as part an aggregation to return any single value. When run at scale, there is some randomness to the value that is returned from the aggregated groupings, although randomness in not guaranteed.

In a flat aggregation, in which no aggregate function is applied, it selects the first value that it can retrieve from a column, which is the first value. This function has limited value outside of an aggregation. See Pivot Transform.

Input column might be of Integer, Decimal, String, Object, or Array type.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

any(myRating)

Output: Returns a single value from the myRating column.

Syntax and Arguments

any(function_col_ref) [group:group_col_ref] [limit:limit_count]

Argument	Required?	Data Type	Description
function_col_ref	Y	string	Name of column to which to apply the function

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

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

function_col_ref

Name of the column from which to extract a value based on the grouping.

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

Usage Notes:

Required?	Data Type	Example Value
Yes	String (column reference)	myValues

Examples

Tip

For additional examples, see Common Tasks.

Example - Aggregating on one customer per month

You want to do some sampling of customer orders on a monthly basis. For your sample, you want to select the sum of orders for one customer each month.

Source:

Here are the orders for 1Q 2015:

OrderId	Date	CustId	Qty
1001	1/8/15	C0001	12
1002	2/12/15	C0002	65
1003	1/16/15	C0004	23
1004	1/31/15	C0002	92
1005	2/2/15	C0005	56
1006	3/2/15	C0006	83
1007	3/16/15	C0005	62
1008	2/21/15	C0002	43
1009	3/28/15	C0001	86

Transformation:

To aggregate this date by month, you must extract the month value from the Date column:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	dateformat(Date, 'MMM')
Parameter: New column name	'month_Date'

You should now have a new column with three-letter month abbreviations. You can use the following aggregation to gather the sum of one customer's orders for each month:

Transformation Name	`Pivot columns`
Parameter: Row labels	month_Date
Parameter: Values	any(CustId),sum(Qty)
Parameter: Max columns to create	1

Results:

month_Date	any_CustId	sum_Qty
Jan	C0001	127
Feb	C0002	164
Mar	C0006	211

In this section: