Page tree

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

Compare with Current View Page History

« Previous Version 2 Current »



Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

Computes the sum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.

When this function is invoked, all of the values in the input array are passed to the corresponding columnar function. Some restrictions may apply. See SUM Function.

Basic Usage

Literal example:

derive type:single value:LISTSUM([0,0,2,4,6,8,10,12,14,16,18,20]) as:'myArraySum'

Output: Generates an output column containing the sum of all values in the literal array: 110.

Column example:

derive type:single value:LISTSUM(myArray) as:'myArraySum'

Output: Generates an output column containing the sum of all values in the arrays of the myArray column.

Syntax

derive type:single value:LISTSUM(array_ref)

ArgumentRequired?Data TypeDescription
array_refYArrayArray literal, reference to column containing arrays, or function returning an array

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

array_ref

Reference to an array can be an array literal, function returning an array, or a single column containing arrays.

  • If the input is not a valid numeric array, null values are returned.
  • Non-numerical values within an input array are not factored in the computation.
  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
Yes

Array

myArray

Examples

Example - Math functions for lists (arrays)

This example describes how to generate random array (list) data and then to apply the following math functions to your arrays.

Source:

For this example, you can generate some randomized data using the following steps. First, you need to seed an array with a range of values using the RANGE function:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula RANGE(5, 50, 5)
Parameter: New column name 'myArray1'

Then, unpack this array, so you can add a random factor:

Transformation Name Unnest Objects into columns
Parameter: Column myArray1
Parameter: Paths to elements '[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]'
Parameter: Remove elements from original true
Parameter: Include original column name true

Add the randomizing factor. Here, you are adding randomization around individual values:  x-1 < x < x+4.

Transformation Name Edit column with formula
Parameter: Columns myArray1_0~myArray1_8
Parameter: Formula IF(RAND() > 0.5, $col + (5 * RAND()), $col - RAND())

To make the numbers easier to manipulate, you can round them to two decimal places:

Transformation Name Edit column with formula
Parameter: Columns myArray1_0~myArray1_8
Parameter: Formula ROUND($col, 2)

Renest these columns into an array:

Transformation Name Nest columns into Objects
Parameter: Columns myArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8
Parameter: Nest columns to Array
Parameter: New column name 'myArray2'

Delete the unused columns:

Transformation Name Delete columns
Parameter: Columns myArray1_0~myArray1_8,myArray1
Parameter: Action Delete selected columns

Your data should look similar to the following:

myArray2
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"]
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"]
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"]
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"]
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"]
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"]
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"]
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"]
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"]
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"]

Transformation:

These steps demonstrate the individual math functions that you can apply to your list data without unnesting it:

NOTE: The NUMFORMAT function has been wrapped around each list function to account for any floating-point errors or additional digits in the results.

 

Sum of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTSUM(myArray2), '#.##')
Parameter: New column name 'arraySum'

Minimum of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTMIN(myArray2), '#.##')
Parameter: New column name 'arrayMin'

Maximum of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTMAX(myArray2), '#.##')
Parameter: New column name 'arrayMax'

Average of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTAVERAGE(myArray2), '#.##')
Parameter: New column name 'arrayAvg'

Variance of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTVAR(myArray2), '#.##')
Parameter: New column name 'arrayVar'

Standard deviation of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTSTDEV(myArray2), '#.##')
Parameter: New column name 'arrayStDv'

Mode (most common value) of all values in the array (list):

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula NUMFORMAT(LISTMODE(myArray2), '#.##')
Parameter: New column name 'arrayMode'

Results:

Results for the first four math functions:

myArray2arrayAvgarrayMaxarrayMinarraySum
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"]25.0444.638.29225.33
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"]28.9349.018.32260.4
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"]24.5844.584.55221.19
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"]29.7749.849.22267.94
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"]28.448.368.75255.63
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"]29.6249.768.47266.55
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"]24.9844.994.93224.85
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"]29.3949.984.65264.49
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"]29.4249.627.8264.76
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"]25.4444.969.32229

Results for the statistical functions:

 

myArray2 arrayModearrayStDvarrayVar 
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"] 12.32151.72
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"] 13.03169.78
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"] 12.92166.8
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"] 13.02169.46
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"] 12.84164.95
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"] 13.14172.56
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"] 12.92166.93
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"] 13.9193.16
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"] 13.23175.08
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"] 12.21149.17

Since all values are unique within an individual array, there is no most common value in any of them, which yields empty values for the arrayMode column.

 

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 1 rates

  • No labels

This page has no comments.