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
Required: Yesoffset
Required: NolimitDROP
Required: Yesrows
Required: NocolsEXPAND
Required: Yesrows
Required: Nocols
Required: Nopad_withHSTACK
Required: YesarraysVSTACK
Required: YesarraysTRANSPOSEnone
TOCOL
Required: Noignore
Required: Noscan_by_columnTOROW
Required: Noignore
Required: Noscan_by_columnARRAY
Required: Yeskey
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
TAKE | list | The chosen items of the list | items.TAKE("3") |
DROP | list | The list without those items | grid.DROP(1, 1) |
EXPAND | list | The list padded to that size. A list already that long is unchanged | grid.EXPAND(3, 4, 0) |
HSTACK | list or grid | The lists side by side: one longer list when all are plain lists, otherwise a grid with a column for each | a.HSTACK(b, c) |
VSTACK | list or grid | The lists one under another: one longer list when all are plain lists, otherwise a grid, short rows padded with empty values | a.VSTACK(b, c) |
TRANSPOSE | grid | The grid with its rows and columns swapped. A plain list becomes a column | sales.TRANSPOSE() |
TOCOL | list | A grid or list as one list | sales.TOCOL(1, 1) |
TOROW | list | A grid or list as one list, in the same order as TOCOL | sales.TOROW() |
ARRAY | list | That field from every record in the list | order.lines.ARRAY("sku") |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | The value in front of the function is not a list or a grid, or ARRAY was given a value that is not a list | Pass 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. TRANSPOSEreads a grid's width from its first row.HSTACKandVSTACKtake the other lists by the names of the variables that hold them, any number of them.EXPANDpads a plain list to a length; the width applies to a grid.TOCOLandTOROWgive the same plain list.ARRAYtakes 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.