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
ヒント
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 | |
|---|---|
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 | |
|---|---|
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 |