Page tree

 

Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

Contents:


Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order.
  • This function calculates based on the outer layer of an array. If your array is nested, the sorting of inner elements is not factored.

Basic Usage

Array literal reference example:

derive type:single value:ARRAYSORT([A,B,C,D],descending)

Output: Generates the following array in the new column: [D,C,B,A]

Column reference example:

derive type:single value:ARRAYSORT(myArrays,ascending) as:'myArrays_ascending'

Output: Generates the new myArrays_ascending column containing the arrays listed in the myArrays column sorted in ascending order.

Syntax

derive type:single value:ARRAYSORT(array_ref,order_enum)

ArgumentRequired?Data TypeDescription
array_refYstringName of Array column, Array literal, or function returning an Array to apply to the function
order_enumYstring (enumerated value)

Order is defined as either:

  • ascending (default)
  • descending

For more information on syntax standards, see Language Documentation Syntax Notes.

array_ref

Name of the array column, array literal, or function returning an array whose array values you wish to sort.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference or function) or array literalmyArray1

order_enum

String literal indicating the order by which the referenced arrays should be sorted:

  • ascending - (default) lowest values for the valid data type are listed first. 
  • descending - Null/empty values are sorted first, followed by mismatched values. Then, the array values that are valid for the specified data type are listed in descending order.  
  • For more information on the rules of sorting, see Sort Order.

Usage Notes:

Required?
Data Type
Example Value
NoString (enumerated value)descending


Examples

Example - Student progress across tests

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]

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 transform sorts the array values in highest to lowest score:

set column: Scores value: 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:

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

derive type:single value:ARRAYELEMENTAT(Scores,3) as:'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:

derive type:single value:SUBTRACT(highestScore,lowestScore) as:'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

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 2 rates

This page has no comments.