EXAMPLE - ARRAYLEN and ARRAYELEMENTAT Functions

This example covers the following functions:

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 change in scores between first and fourth tests.
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]

Transform:

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

derive type:single value:IF(ARRAYLEN(Scores) < 4,"incomplete","") as:'Error'

This test flags Cameron Charles only.

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

derive type:single value:ARRAYELEMENTAT(Scores,0) as:'Scores1'

derive type:single value:ARRAYELEMENTAT(Scores,3) as:'Scores4'

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:

derive type:single value:SUBTRACT(Scores4,Scores1) as:'Scores_change'


Results:

LastNameFirstNameScoresErrorScores1Scores4Scores_change
AllenAmanda[79, 83,87,81] 79812
BellBobby[85, 92, 94, 98] 859813
CharlesCameron[88,81,85]incomplete88
DudleyDanny[82,88,81,77] 8277-5
EllisEvan[91,93,87,93] 91932
Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation
Need help? Visit our support page.