##### Page tree

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

Version 41

Trifacta SaaS

Contents:

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

Functions:

ItemDescription
\$strPageTitle

Error rendering macro 'excerpt-include'

No link could be created for '\$strPageTitle'.

\$strPageTitle

Error rendering macro 'excerpt-include'

No link could be created for '\$strPageTitle'.

\$strPageTitle

Error rendering macro 'excerpt-include'

No link could be created for '\$strPageTitle'.

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` `Single row formula` `IF(ARRAYLEN(Scores) < 4,"incomplete","")` `'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` `Scores` `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` `Single row formula` `ARRAYELEMENTAT(Scores,0)` `'highestScore'`

Transformation Name `New formula` `Single row formula` `ARRAYELEMENTAT(Scores,3)` `'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` `Single row formula` `SUBTRACT(highestScore,lowestScore)` `'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