Reshaping functions

Cut, pad, join and turn lists and grids of values.

Syntax

These are array functions, written as a method on a variable that holds the list: after LET items = [...], write items.NAME(...), or order.lines.NAME(...) for a list inside a record. The list is what is written before the function's name, so it is not one of the arguments in the brackets. Written with the list first inside brackets, SORT(items, ...), the name is the SORT or TAKE clause of a statement, not this function.

TAKE(offset, [limit])
DROP(rows, [cols])
EXPAND(rows, [cols], [pad_with])
HSTACK(arrays)
VSTACK(arrays)
TRANSPOSE()
TOCOL([ignore], [scan_by_column])
TOROW([ignore], [scan_by_column])
ARRAY(key)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • TAKE

    offset

    Required: Yes
  • limit

    Required: No
  • DROP

    rows

    Required: Yes
  • cols

    Required: No
  • EXPAND

    rows

    Required: Yes
  • cols

    Required: No
  • pad_with

    Required: No
  • HSTACK

    arrays

    Required: Yes
  • VSTACK

    arrays

    Required: Yes
  • TRANSPOSE

    none

  • TOCOL

    ignore

    Required: No
  • scan_by_column

    Required: No
  • TOROW

    ignore

    Required: No
  • scan_by_column

    Required: No
  • ARRAY

    key

    Required: Yes

Examples

Six products in sales order: the first three, the three after skipping one, everything but the first two, and everything but the last.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
TAKElistThe chosen items of the listitems.TAKE("3")
DROPlistThe list without those itemsgrid.DROP(1, 1)
EXPANDlistThe list padded to that size. A list already that long is unchangedgrid.EXPAND(3, 4, 0)
HSTACKlist or gridThe lists side by side: one longer list when all are plain lists, otherwise a grid with a column for eacha.HSTACK(b, c)
VSTACKlist or gridThe lists one under another: one longer list when all are plain lists, otherwise a grid, short rows padded with empty valuesa.VSTACK(b, c)
TRANSPOSEgridThe grid with its rows and columns swapped. A plain list becomes a columnsales.TRANSPOSE()
TOCOLlistA grid or list as one listsales.TOCOL(1, 1)
TOROWlistA grid or list as one list, in the same order as TOCOLsales.TOROW()
ARRAYlistThat field from every record in the listorder.lines.ARRAY("sku")
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001The value in front of the function is not a list or a grid, or ARRAY was given a value that is not a listPass a list or a grid
Limitations
  • The value in front of the function must be a list; a plain value is refused with HTQL-2001.
  • TRANSPOSE reads a grid's width from its first row.
  • HSTACK and VSTACK take the other lists by the names of the variables that hold them, any number of them.
  • EXPAND pads a plain list to a length; the width applies to a grid.
  • TOCOL and TOROW give the same plain list.
  • ARRAY takes the field from each record; a plain value in the list stays as it is.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Use TAKE after SORT for a top N list.
  • Use DROP(1) to remove a header row from rows read from a file.
  • Hold each grid in a variable so HSTACK and VSTACK can name it.