Home Search

DriveWorks Solo 21
Functions - Table

Send Feedback

Functions - Table

The following Table functions have been added to DriveWorks 12.

TableAppendColumns

Appends the columns from the given tables in order, and returns the result.

Syntax

TableAppendColumns(First Table As Table, Second Table As Table, Third Table As Table)

Where:

First Table is the first set of columns for the new table.

Second Table is the second set of columns for the new table.

Third Table (Optional) is the third set of columns for the new table.

Examples

RuleMeaning
TableAppendColumns(DWLookupCustomers, DWLookupContacts)Will return an array of the columns from the table Customers and Contacts.

TableAppendRows

Appends the rows from the given tables in order, and returns the result.

Syntax

TableAppendRows(First Table As Table, Second Table As Table, Third Table As Table)

Where:

First Table is the first set of rows for the new table.

Second Table is the second set of rows for the new table.

Third Table (Optional) is the third set of rows for the new table.

Examples

RuleMeaning
TableAppendRows(DWLookupCustomers, DWLookupContacts)Will return an array of the rows from the table Customers and Contacts.

TableBreak

Breaks the table into two at, or after the row matching the given condition, and returns either the top or bottom half.

Syntax

TableBreak(Table or Table Name As Table, Column Index As Double, Condition As String, Top As Boolean, Include Break Row As Boolean)

Where:

Table or Table Name is the Table to break.

Column Index is the number of the column in which to check for a match.

Condition is the condition that defines the break.

Top will return the top portion of the break, when TRUE. Otherwise it will return the bottom portion when FALSE.

Include Break Row will include the row from which the break starts, when TRUE.

Examples

RuleMeaning
TableBreak(DWLookupCustomers, 2, "DriveWorks", TRUE, TRUE)Will return an array of the rows from the table Customers up to the entry for DriveWorks, and will include the row at which the break occurs.

TableColumn

Constructs a table containing a single column with a blank header, followed by the given values.

Syntax

TableColumn(Collection of Values As String)

Where:

Collection of Values are the values to present as rows in the table, where each value is separated by a comma.

Examples

RuleMeaning
TableColumn("Red","Green","Blue")Will return a single column array, with a blank header, with the values Red, Green and Blue on separate rows.

TableCombine

Combines the rows from the given tables into a single table, re-using columns where they match.

Syntax

TableCombine(Collection of Tables As Tables)

Where:

Collection of Tables are the tables to combine, where each table name is separated by a comma.

Examples

RuleMeaning
TableCombine(DWLookupCustomers, DWLookupContacts)Will return an array comprising of the rows from the Customers table followed by the rows from the Contacts table. Duplicate columns will become one column.

TableFilter

Eliminates non-matching values from the table.

Syntax

TableFilter(Table or Table Name As Table, Column Index 1 As Double, Condition 1 As String, Column Index 2 As Double, Condition 2 As String, Column Index 3 As Double, Condition 3 As String)

Where:

Table or Table Name is the Table to filter.

Column Index 1 is the first column to filter on.

Condition 1 is the condition to filter column 1 on.

Column Index 2 (optional) is the second column to filter on.

Condition 2 (optional) is the condition to filter column 2 on.

Column Index 3 (optional) is the third column to filter on.

Condition 3 (optional) is the condition to filter column 3 on.

Examples

RuleMeaning
TableFilter(DWLookupCustomers,1,"D*")Will filter the table Customers to return an array containing only the rows whose value in column 1 begins with D.

TableFilterAll

Eliminates non-matching values from the table.

Syntax

TableFilterAll(Table or Table Name As Table, Condition As Table)

Where:

Table or Table Name is the Table to filter.

Condition is the name of a table containing conditions. The table should not have a header row. Each row should have 2 columns where the first is the column index number to check and the second column is the condition to filter on.

Examples

RuleMeaning
TableFilterAll(DWLookupCustomers,DWLookupFilterList)Will return an array filtered to display all values from the Customers table that match the conditions given in the table FilterList.

TableFormat

Applies a format to all values in a specified column of a table.

Syntax

TableFormat(Table or Table Name As Table, Column Index As Double, Format As String)

Where:

Table or Table Name is the Table to format.

Column Index is the number of the column in which the formatting will be applied.

Format is the formatting string to use. Where:

  • "0000.00" - Formats a number with up to four leading zeroes, and two decimal places.
  • "#.00" - Formats a number to two decimal places.
  • "DDDDMMYY-HHMMSS" - Formats a date

Examples

RuleMeaning
TableFormat(DWLookupData,4,"dd/mm/yy")Will return an array from the table Data, with the values in column 4 formatted as a date.

TableGetDataRows

Gets everything except the header row from the given table.

Syntax

TableGetDataRows(Table or Table Name As Table)

Where:

Table or Table Name is the Table from which to get the rows.

Examples

RuleMeaning
TableGetDataRows(DWLookupCustomers)Will return an array of the rows from the Customers table, without the header row.

TableGetHeaderRow

Gets the header row from the given table, as a data row.

Syntax

TableGetHeaderRow(Table As Table, As Data Row As Boolean)

Where:

Table is the Table from which to get the header row.

As Data Row (optional) is TRUE by default and will return the header row as a data row. If set to FALSE it will return it as a header row.

Examples

RuleMeaning
TableGetHeaderRow(DWLookupCustomers, TRUE)Will return an array comprising of two rows; the first row (header row) containing empty values, the second row (data row) containing the values retrieved from the header row in the Customers group table.
TableGetHeaderRow(DWLookupCustomers, FALSE)Will return an array comprising of one row, which will be the header row retrieved from the Customers group table.

TableGetRows

Returns a new table with only the specified rows.

Syntax

TableGetRows(Table As Table, First Row As Double, Rows to Take As Double)

Where:

Table is the Table from which to get the rows.

First Row is the number of the first row to take.

Rows to Take is the number of rows to retrieve.

Examples

RuleMeaning
TableGetRows(DWLookupCustomers, 5, 25)Will return an array from the Customers table, starting at row 5 and containing the next 25 rows.

TableGetValue

Gets the value from the first row and column of the given table.

Or

Gets the value from the first row and specified column of the given table.

Or

Gets the value from the specified row and column of the given table.

Syntax

TableGetValue(Table As Table, Column Index As Double, Row Index As Double)

Where:

Table is the Table from which to get the value.

Column Index (optional) is the number of the column from which to retrieve the value. By default this will be the first column.

Row Index (optional) is the number of the row from which to retrieve the value. By default this will be the first row.

Examples

RuleMeaning
TableGetValue(DWLookupCustomers)Will return the value that exists in the Customers table at column 1, row 1.
TableGetValue(DWLookupCustomers, 2)Will return the value that exists in the Customers table at column 2, row 1.
TableGetValue(DWLookupCustomers, 3, 8)Will return the value that exists in the Customers table at column 3, row 8.

TableJoin

Performs a join of the specified tables based on matching values in columns in each of the two tables.

Syntax

TableJoin(Left Table As Table, Left Match Column As Double, Right Table As Table, Left Match Column As Double, Join Type As String)

Where:

Left Table is the Table to join on the left.

Left Match Column is the number of the column which contains values that will be matched against values in the right table.

Right Table is the Table to join on the right.

Left Match Column is the number of the column which contains values that will be matched against values in the left table.

Join Type (optional) is the type of join to perform (either "Inner" or "Outer"), if not specified, by default it will perform an inner join:

  • Inner - returns rows from both tables where there is a match between the columns in both tables
  • Outer - returns all rows from the left table and from the right table

Examples

RuleMeaning
TableJoin(DWLookupCustomers, 3, DWLookupCurrency, 4, "Inner")Will return an inner join of the tables Customers and Currency. Values in column 3 of the Customers table are matched with values in the Currency table. Values in column 4 of the Currency table are matched with values in the Customers table.

TableRemoveRow

Removes a row from the given table.

Syntax

TableRemoveRow(Table As Table, Row Index As Double)

Where:

Table is the Table from which to remove the row.

Row Index is the number of the row to remove.

Examples

RuleMeaning
TableRemoveRow(DWLookupCustomers, 3)Will return an array of the Customers table with row 3 removed.

TableReplaceHeaderRow

Replaces the header row in the given table.

Syntax

TableReplaceHeaderRow(Table As Table, Replacement Row As String)

Where:

Table is the Table in which to replace the header row..

Replacement Row is the row that will replace the header row.

Examples

RuleMeaning
TableReplaceHeaderRow(DWLookupCustomers, TableRow("CN","ConN","CY","ST","CT","ID"))Will replace the header row of the Customers table with the values coming from the function TableRow("CN","ConN","CY","ST","CT","ID").

TableReplaceRow

Replaces a row in the given table.

Syntax

TableReplaceRow(Table As Table, Row Index As Double, Replacement Row As Double)

Where:

Table is the Table in which to replace the row.

Row Index is the index of the row to replace.

Replacement Row is either a single row or multiple rows that will replace the row at the specified index.

Examples

RuleMeaning
TableReplaceRow(DWLookupCustomers, 2, TableRow("ACME","Ohio","USA"))Will replace the second row in the Customers table with the values coming from the function TableRow("ACME","Ohio","USA").

TableReverse

Reverses the order of the rows in the given table.

Syntax

TableReverse(Table As Table)

Where:

Table is the Table to reverse the row order of.

Examples

RuleMeaning
TableReverse(DWLookupCustomers)Will reverse the existing order of the rows in the Customers table.

TableRow

Constructs a table containing a header row, followed by a single data row containing the given values.

Syntax

TableRow(Collection of Values As String)

Where:

Collection of Values are the values to include in the data row.

Examples

RuleMeaning
TableRow("Red","Green","Blue")Will return an array consisting of two rows; a blank header row and a row with the values Red, Green and Blue.

TableSelectColumns

Retrieves the columns from the given table in the specified order, and returns the result.

Syntax

TableSelectColumns([Table] As Table, [Collection of Columns] As Double)

Where:

Table is the table from which to retrieve the columns.

Collection of Columns are the indices of the columns that should be in the result table, these can be in any order, e.g. 3,2,1 to switch the first and third columns around.

Examples

RuleMeaning
TableSelectColumns(DWLookupCustomers,1,3,4)Will return an array containing the columns 1,3 and 4 and the records from the corresponding rows, from the Customers table.

TableSequence

Generates a table with exactly one column, with an empty header, followed by the specified number of rows, where the first row has the value 1, and each row after is assigned an incrementing value.

Syntax

TableSequence(Row Count As Double)

Where:

Row Count is the number of data rows to be created in the table.

Examples

RuleMeaning
TableSequence(50)Will return an array of a single column with 50 rows, each containing the values 1 through to 50.

TableSkipRows

Returns a new table where the specified number of rows have been removed from the start of the given table.

Syntax

TableSkipRows([Table] As Table, [Rows to Skip] As Double)

Where:

Table is the table from which to take the rows.

Rows to Skip is the number of rows from the start of the input table that should be omitted.

Examples

RuleMeaning
TableSkipRows(DWLookupCustomers,14)Will return an array of the table Customers with the first 14 rows omitted.

TableSubstitute

Substitutes text found in a column in the provided table.

Syntax

TableSubstitute([Table] As Table, [Column Index] As Double, [Old Tex]t As String, [New Text] As String, [Which Instance] As Double, [Ignore Case] As Boolean)

Where:

Table is the table from which to substitute text.

Column Index is the index of the column to have text substituted.

Old Text is the old text within the column to be substituted.

New Text is the new text that is to substitute the old text.

Which Instance (optional) is a number representing the occurrence of the old text that is to be replaced. 0 means every occurrence of the old text will be replaced.

Ignore Case (optional) is TRUE to ignore case when replacing.

Examples

RuleMeaning
TableSubstitute(DWLookupCustomers,1,"All Stars","All Stars Inc.",0,TRUE)Will return an array of the table Customers with the old text "All Stars" replaced with "All Stars Inc.".

TableTakeRows

Returns a new table with only the specified number of rows.

Syntax

TableTakeRows([Table] As Table, [Rows to Take] As Double)

Where:

Table is the table to take the rows from.

Rows to Take is the number of rows from the input table that should be present in the result table.

Examples

RuleMeaning
TableTakeRows(DWLookupCustomers,10)Will return an array of the table Customers containing the first 10 rows.

TableWithSequence

Prepends the given table with a column containing sequence numbers. This is a convenient wrapper around using TableAppendColumns and TableSequence.

Syntax

TableWithSequence([Table] As Table)

Where:

Table is the table to add a sequence column.

Examples

RuleMeaning
TableWithSequence(DWGroupTableCustomers)Will return an array of the table Customers with a column added , that has a blank header, which contains sequential numbers.