Page tree

Release 9.2


Contents:

   

This example illustrates how to return n-based number of elements in an array.


Functions:

ItemDescription
ARRAYLEN Function Computes the number of elements in the arrays in the specified column, array literal, or function that returns an array.
ARRAYELEMENTAT Function Computes the 0-based index value for an array element in the specified column, array literal, or function that returns an array.
ARRAYSORT Function Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order.

Source:

Here are some student test scores. Individual scores are stored in the Scores column. You want to:

  1. Flag the students who have not taken four tests.
  2. Compute the range in scores for each student.
LastNameFirstNameScores
AllenAmanda[79, 83,87,81]
BellBobby[85, 92, 94, 98]
CharlesCameron[88,81,85]
DudleyDanny[82,88,81,77]
EllisEvan[91,93,87,93]

Transformation:

First, you want to flag the students who did not take all four tests:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula IF(ARRAYLEN(Scores) < 4,"incomplete","")
Parameter: New column name 'Error'

This test flags Cameron Charles only.

The following transform sorts the array values in highest to lowest score:

Transformation Name Edit column with formula
Parameter: Columns Scores
Parameter: Formula ARRAYSORT(Scores, 'descending')


The following transforms extracts the first (highest) and last (lowest) value in each student's test scores, provided that they took four tests:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYELEMENTAT(Scores,0)
Parameter: New column name 'highestScore'

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula ARRAYELEMENTAT(Scores,3)
Parameter: New column name 'lowestScore'

Tip: You could also generate the Error column when the Scores4 column contains a null value. If no value exists in the array for the ARRAYELEMENTAT function, a null value is returned, which would indicate in this case an insufficient number of elements (test scores).

You can now track change in test scores:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula SUBTRACT(highestScore,lowestScore)
Parameter: New column name 'Score_range'

Results:

LastNameFirstNameScoresErrorlowestScorehighestScoreScore_range
AllenAmanda[87,83,81,79] 79878
BellBobby[98,94,92,85] 859813
CharlesCameron[88,85,81]incomplete 88 
DudleyDanny[88,82,81,77] 778811
EllisEvan[93,93,91,87] 87936


See Also for EXAMPLE - ARRAYLEN and ARRAYELEMENTAT Functions:


This page has no comments.