Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

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 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:

D trans
RawWrangletrue
p03Value'Error'
Typestep
WrangleTextderive type:single value:IF(ARRAYLEN(Scores) < 4,"incomplete","") as:'Error'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueIF(ARRAYLEN(Scores) < 4,"incomplete","")
p03NameNew column name
SearchTermNew formula

This test flags Cameron Charles only.

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

D trans
RawWrangletrue
Typestep
WrangleTextset column: Scores value: ARRAYSORT(Scores, 'descending')
p01NameColumns
p01ValueScores
p02NameFormula
p02ValueARRAYSORT(Scores, 'descending')
SearchTermEdit column with formula


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

D trans
RawWrangletrue
p03Value'highestScore'
Typestep
WrangleTextderive type:single value:ARRAYELEMENTAT(Scores,0) as:'highestScore'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueARRAYELEMENTAT(Scores,0)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'lowestScore'
Typestep
WrangleTextderive type:single value:ARRAYELEMENTAT(Scores,3) as:'lowestScore'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueARRAYELEMENTAT(Scores,3)
p03NameNew column name
SearchTermNew formula

Tip

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:

D trans
RawWrangletrue
p03Value'Score_range'
Typestep
WrangleTextderive type:single value:SUBTRACT(highestScore,lowestScore) as:'Score_range'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueSUBTRACT(highestScore,lowestScore)
p03NameNew column name
SearchTermNew formula

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