Date: Wed, 29 Mar 2023 11:07:22 +0000 (UTC) Message-ID: <1393890122.93199.1680088042717@1323b43bed1d> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_93198_850818171.1680088042717" ------=_Part_93198_850818171.1680088042717 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - LIST Math Functions

# EXAMPLE - LIST Math Functions

This example describes how to generate random array (list) data and= then to apply statistical functions specifically created for arrays.

Functions:

=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
LISTSUM Function Computes the sum of all numeric values found in= input array. Input can be an array literal, a column of arrays, or a funct= ion returning an array. Input values must be of Integer or Decimal type. =20
LISTMIN Function Computes the minimum of all numeric values foun= d in input array. Input can be an array literal, a column of arrays, or a f= unction returning an array. Input values must be of Integer or Decimal type= .
LISTMAX Function Computes the maximum of all numeric values foun= d in input array. Input can be an array literal, a column of arrays, or a f= unction returning an array. Input values must be of Integer or Decimal type= .
LISTAVERAGE Function Computes the average of all numeric values foun= d in input array. Input can be an array literal, a column of arrays, or a f= unction returning an array. Input values must be of Integer or Decimal type= .
LISTVAR Function Computes the variance of all numeric values fou= nd 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 typ= e.
LISTSTDEV Function Computes the standard deviation of all numeric = values found in input array. Input can be an array literal, a column of arr= ays, or a function returning an array. Input values must be of Integer or D= ecimal type.
LISTMODE Function Computes the most common value of all numeric v= alues found in input array. Input can be an array literal, a column of arra= ys, or a function returning an array. Input values must be of Integer or De= cimal type.

Also:

=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
RANGE Function Computes an array of integers, from a beginning= integer to an end (stop) integer, stepping by a third parameter.
RAND Function The `RAND` function genera= tes a random real number between 0 and 1. The function accepts an opti= onal integer parameter, which causes the same set of random numbers to be g= enerated with each job execution.
ROUND Function Rounds input value to the nearest integer. = ;Input can be an Integer, a Decimal, a column reference, or an express= ion. Optional second argument can be used to specify the number of dig= its to which to round.

Source:

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `RANGE(5, 50, 5)` `'myArray1'`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Unnest Objects into columns` `myArray1` ```'', '', '', '', '', '', = '', '', '', ''``` `true` `true`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `myArray1_0~myArray1_8` ```IF(RAND() > 0.5, \$col + (5 * RAND()), \$= col - RAND())```
=20

To make the numbers easier to manipulate, you can round them to two deci= mal places:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `myArray1_0~myArray1_8` `ROUND(\$col, 2)`
=20

Renest these columns into an array:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Nest columns into Objects` ```myArray1_0, myArray1_1, myArray1_2, myArra= y1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8```=20 `Array` `'myArray2'`
=20

Delete the unused columns:

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

Your data should look similar to the following:

=20 =20
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 ea= ch list function to account for any floating-point errors or additional dig= its in the results.

Sum of all values in the array (list):

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTSUM(myArray2), '#.##')` `'arraySum'`
=20

Minimum of all values in the array (list):

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTMIN(myArray2), '#.##')` `'arrayMin'`
=20

Maximum of all values in the array (list):

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTMAX(myArray2), '#.##')` `'arrayMax'`
=20

Average of all values in the array (list):

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTAVERAGE(myArray2), '#.##')` `'arrayAvg'`
=20

Variance of all values in the array (list):

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTVAR(myArray2), '#.##')` `'arrayVar'`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTSTDEV(myArray2), '#.##')` `'arrayStDv'`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `NUMFORMAT(LISTMODE(myArray2), '#.##')` `'arrayMode'`
=20

Results:

Results for the first four math functions:

=20 =20 =20 =20 =20 =20
myArray2 arrayAvg arrayMax arrayMin arraySum
["8.29","9.63","14.63","19.63","24.63","29.63","= 34.63","39.63","44.63"] 25.04 44.63 8.29 225.33
["8.32","14.01","19.01","24.01","29.01","34.01",= "39.01","44.01","49.01"] 28.93 49.01 8.32 260.4
["4.55","9.58","14.58","19.58","24.58","29.58","= 34.58","39.58","44.58"] 24.58 44.58 4.55 221.19
["9.22","14.84","19.84","24.84","29.84","34.84",= "39.84","44.84","49.84"] 29.77 49.84 9.22 267.94
["8.75","13.36","18.36","23.36","28.36","33.36",= "38.36","43.36","48.36"] 28.4 48.36 8.75 255.63
["8.47","14.76","19.76","24.76","29.76","34.76",= "39.76","44.76","49.76"] 29.62 49.76 8.47 266.55
["4.93","9.99","14.99","19.99","24.99","29.99","= 34.99","39.99","44.99"] 24.98 44.99 4.93 224.85
["4.65","14.98","19.98","24.98","29.98","34.98",= "39.98","44.98","49.98"] 29.39 49.98 4.65 264.49
["7.80","14.62","19.62","24.62","29.62","34.62",= "39.62","44.62","49.62"] 29.42 49.62 7.8 264.76
["9.32","9.96","14.96","19.96","24.96","29.96","= 34.96","39.96","44.96"] 25.44 44.96 9.32 229

Results for the statistical functions:

=20 =20 =20 =20 =20
myArray2  arrayMode arrayStDv arrayVar
["8.29","9.63","14.63","19.63","24.63","29.63","= 34.63","39.63","44.63"]   12.32 151.72
["8.32","14.01","19.01","24.01","29.01","34.01",= "39.01","44.01","49.01"]   13.03 169.78
["4.55","9.58","14.58","19.58","24.58","29.58","= 34.58","39.58","44.58"]   12.92 166.8
["9.22","14.84","19.84","24.84","29.84","34.84",= "39.84","44.84","49.84"]   13.02 169.46
["8.75","13.36","18.36","23.36","28.36","33.36",= "38.36","43.36","48.36"]   12.84 164.95
["8.47","14.76","19.76","24.76","29.76","34.76",= "39.76","44.76","49.76"]   13.14 172.56
["4.93","9.99","14.99","19.99","24.99","29.99","= 34.99","39.99","44.99"]   12.92 166.93
["4.65","14.98","19.98","24.98","29.98","34.98",= "39.98","44.98","49.98"]   13.9 193.16
["7.80","14.62","19.62","24.62","29.62","34.62",= "39.62","44.62","49.62"]   13.23 175.08
["9.32","9.96","14.96","19.96","24.96","29.96","= 34.96","39.96","44.96"]   12.21 149.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.

=20
=20

=20

=20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
=20

=20
=20
=20

------=_Part_93198_850818171.1680088042717--