Skip to main content

Sort Function

The Sort function orders your data rows based on one or more fields. It's essential for preparing data for presentation, ensuring correct processing order, or controlling which rows are kept by Distinct.

How It Works​

Sort reorders all rows based on your specified criteria:

  • Rows are rearranged but not added or removed
  • All fields remain unchanged
  • Multiple sort levels create hierarchical ordering

Adding a Sort​

  1. Drag Sort from the Functions section of the Element Panel
  2. Connect it to your data source
  3. Click the Sort node to configure sort fields

Configuration Panel​

Sort Fields​

Define the ordering criteria:

Adding a Sort Field​

  1. Click Add Sort Field
  2. Select a field from the dropdown
  3. Choose sort direction (Ascending or Descending)

Sort Direction​

Ascending (A-Z, 0-9, oldest-newest):

  • Text: A, B, C... Z
  • Numbers: 1, 2, 3... 100
  • Dates: Jan 1, Jan 2... Dec 31

Descending (Z-A, 9-0, newest-oldest):

  • Text: Z, Y, X... A
  • Numbers: 100, 99... 2, 1
  • Dates: Dec 31... Jan 2, Jan 1

Multiple Sort Levels​

Sort by multiple fields in priority order:

Example Configuration:

FieldDirection
RegionAscending
OrderDateDescending
CustomerNameAscending

Result:

  1. First sorted by Region (A-Z)
  2. Within each Region, sorted by OrderDate (newest first)
  3. Within same Region and Date, sorted by CustomerName (A-Z)

Priority matters: First field is primary sort, second is secondary, etc.

Data Type Sorting​

Text Sorting​

Alphabetical order based on character codes:

  • Case-sensitive: Uppercase before lowercase ("A" before "a")
  • Special characters have defined positions
  • Numbers in text sort as characters ("10" before "2")

For numeric-like text, transform first:

SortKey = PADLEFT(${OrderNumber}, 10, "0")

Number Sorting​

Numeric order:

  • Ascending: Smallest to largest
  • Descending: Largest to smallest
  • Decimals handled correctly
  • Negatives sorted correctly

Date Sorting​

Chronological order:

  • Ascending: Oldest first (earliest date)
  • Descending: Newest first (latest date)
  • Time component included in comparison

Boolean Sorting​

  • Ascending: False (0), then True (1)
  • Descending: True (1), then False (0)

NULL Handling​

Null values in sort fields:

  • Typically sort first in ascending order
  • Sort last in descending order
  • Behavior may vary by configuration

To control null position:

Transform before Sort:
SortField = COALESCE(${Field}, "ZZZZZ")  // Nulls sort last in ASC
SortField = COALESCE(${Field}, "")       // Nulls sort first in ASC

Common Use Cases​

Chronological Reports​

Scenario: Display orders by date

Sort Configuration:

FieldDirection
OrderDateDescending

Result: Most recent orders first

Alphabetical Lists​

Scenario: Customer directory

Sort Configuration:

FieldDirection
LastNameAscending
FirstNameAscending

Result: Customers sorted by last name, then first name

Top/Bottom Analysis​

Scenario: Find highest values

Sort Configuration:

FieldDirection
AmountDescending

Result: Highest amounts first (combine with limit for "Top 10")

Hierarchical Ordering​

Scenario: Organize by category and name

Sort Configuration:

FieldDirection
CategoryAscending
SubCategoryAscending
ProductNameAscending

Result: Organized hierarchically

Priority Queues​

Scenario: Process high priority first

Sort Configuration:

FieldDirection
PriorityAscending
CreatedDateAscending

Result: High priority, oldest items first

Sort with Other Functions​

Sort + Distinct​

Control which duplicate row to keep:

[Data] → [Sort: ModifiedDate DESC] → [Distinct: ID, Keep First]

Result: Keeps most recently modified record per ID

Sort + Group By​

Sort aggregated results:

[Data] → [GroupBy: Category, SUM(Amount)] → [Sort: TotalAmount DESC]

Result: Categories ordered by total amount

Sort + Filter​

Order then filter (or vice versa):

[Data] → [Sort: Score DESC] → [Filter: Take top 100]

Result: Top 100 highest scores

Performance Considerations​

Sort Early or Late?​

Sort early when:

  • Order affects processing (like Distinct with Keep First)
  • Subsequent operations need ordered data

Sort late when:

  • Only final presentation needs ordering
  • Upstream filtering reduces row count

Memory Usage​

Sorting requires holding data in memory:

  • Large datasets use more memory
  • Consider filtering first to reduce volume

Database Optimization​

When source is a database:

  • Consider sorting in the query (Entity configuration)
  • Database indexes can speed up sorted retrieval

Troubleshooting​

Sort Not Working​

Possible causes:

  1. Wrong data type (text vs number)

    • "10" sorts before "2" as text
    • Convert to number for numeric sort

  2. Case sensitivity

    • "apple" may sort after "Zebra"
    • Use UPPER or LOWER to normalize

  3. Whitespace

    • Leading spaces affect sort position
    • Use TRIM to clean data

Unexpected Order​

Possible causes:

  1. Multiple sort fields in wrong priority

    • First field is primary
    • Reorder as needed

  2. NULL values positioning

    • Add COALESCE to control null placement

  3. Data quality issues

    • Preview data to check actual values

Examples​

Sales Leaderboard​

Sort Configuration:

FieldDirection
TotalSalesDescending

Date-Based Report​

Sort Configuration:

FieldDirection
YearDescending
MonthDescending
DayDescending

Multi-Level Organization​

Sort Configuration:

FieldDirection
DepartmentAscending
TeamAscending
EmployeeNameAscending

Priority + Age Queue​

Sort Configuration:

FieldDirection
PriorityLevelAscending
CreatedDateAscending

Result: Highest priority, oldest items first

Next Steps​