| Excerpt |
|---|
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.
| d-codelang-syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | pivot value: |
|---|
| ANY| any(myRating) group:postal_code limit: 1 |
|
any(myRating) |
Output: Generates a two-column table containing the unique values from the postal_code column and a single Returns a single value from the myRating column for the postal_code value. The limit parameter defines the maximum number of output columns.
| d-code |
|---|
pivot value:ANY| -lang-syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | pivot value:any(function_col_ref) [group:group_col_ref] [limit:limit_count] |
|---|
|
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 |
...
| 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 |
TransformTransformation:
To aggregate this date by month, you must extract the month value from the Date column:
...
| trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'month_Date' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: |
|---|
|
...
| dateformat(Date, 'MMM') as:'month_Date' | | p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | dateformat(Date, 'MMM') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
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:
...
| trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 1 |
|---|
| Type | step |
|---|
| WrangleText | pivot value: |
|---|
|
...
...
| sum(Qty) group: month_Date limit:1 | | p01Name | Row labels |
|---|
| p01Value | month_Date |
|---|
| p02Name | Values |
|---|
| p02Value | any(CustId),sum(Qty) |
|---|
| p03Name | Max columns to create |
|---|
| SearchTerm | Pivot columns |
|---|
|
Results:
| month_Date | any_CustId | sum_Qty |
|---|
| Jan | C0001 | 127 |
| Feb | C0002 | 164 |
| Mar | C0006 | 211 |
