Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0762

D toc

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 s lang vs sql

D s
snippetBasic

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextpivot value:any(myRating) group:postal_code limit: 1

any(myRating)


Output:
 Returns a single value from the myRating column.

D s
snippetSyntax

D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextpivot value:any(function_col_ref) [group:group_col_ref] [limit:limit_count]

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

ArgumentRequired?Data TypeDescription
function_col_refYstringName of column to which to apply the function

D s lang 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.

D s
snippetusage

Required?Data TypeExample Value
YesString (column reference)myValues


D s
snippetExamples

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:

OrderIdDateCustIdQty
10011/8/15C000112
10022/12/15C000265
10031/16/15C000423
10041/31/15C000292
10052/2/15C000556
10063/2/15C000683
10073/16/15C000562
10082/21/15C000243
10093/28/15C000186

Transformation:

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

D trans
RawWrangletrue
p03Value'month_Date'
Typestep
WrangleTextderive type:single value: dateformat(Date, 'MMM') as:'month_Date'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateformat(Date, 'MMM')
p03NameNew column name
SearchTermNew 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:

D trans
RawWrangletrue
p03Value1
Typestep
WrangleTextpivot value: any(CustId),sum(Qty) group: month_Date limit:1
p01NameRow labels
p01Valuemonth_Date
p02NameValues
p02Valueany(CustId),sum(Qty)
p03NameMax columns to create
SearchTermPivot columns

Results: 

month_Dateany_CustIdsum_Qty
JanC0001127
FebC0002164
MarC0006211

D s also
labelaggregate