The following Table functions have been added to DriveWorks 12.
Appends the columns from the given tables in order, and returns the result.
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.
| Rule | Meaning |
|---|---|
| TableAppendColumns(DWLookupCustomers, DWLookupContacts) | Will return an array of the columns from the table Customers and Contacts. |
Appends the rows from the given tables in order, and returns the result.
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.
| Rule | Meaning |
|---|---|
| TableAppendRows(DWLookupCustomers, DWLookupContacts) | Will return an array of the rows from the table Customers and Contacts. |
Breaks the table into two at, or after the row matching the given condition, and returns either the top or bottom half.
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.
| Rule | Meaning |
|---|---|
| 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. |
Constructs a table containing a single column with a blank header, followed by the given values.
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.
| Rule | Meaning |
|---|---|
| TableColumn("Red","Green","Blue") | Will return a single column array, with a blank header, with the values Red, Green and Blue on separate rows. |
Combines the rows from the given tables into a single table, re-using columns where they match.
TableCombine(Collection of Tables As Tables)
Where:
Collection of Tables are the tables to combine, where each table name is separated by a comma.
| Rule | Meaning |
|---|---|
| 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. |
Eliminates non-matching values from the table.
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.
| Rule | Meaning |
|---|---|
| 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. |
Eliminates non-matching values from the table.
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.
| Rule | Meaning |
|---|---|
| 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. |
Applies a format to all values in a specified column of a table.
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:
| Rule | Meaning |
|---|---|
| TableFormat(DWLookupData,4,"dd/mm/yy") | Will return an array from the table Data, with the values in column 4 formatted as a date. |
Gets everything except the header row from the given table.
TableGetDataRows(Table or Table Name As Table)
Where:
Table or Table Name is the Table from which to get the rows.
| Rule | Meaning |
|---|---|
| TableGetDataRows(DWLookupCustomers) | Will return an array of the rows from the Customers table, without the header row. |
Gets the header row from the given table, as a data row.
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.
| Rule | Meaning |
|---|---|
| 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. |
Returns a new table with only the specified rows.
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.
| Rule | Meaning |
|---|---|
| TableGetRows(DWLookupCustomers, 5, 25) | Will return an array from the Customers table, starting at row 5 and containing the next 25 rows. |
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.
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.
| Rule | Meaning |
|---|---|
| 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. |
Performs a join of the specified tables based on matching values in columns in each of the two tables.
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:
| Rule | Meaning |
|---|---|
| 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. |
Removes a row from the given table.
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.
| Rule | Meaning |
|---|---|
| TableRemoveRow(DWLookupCustomers, 3) | Will return an array of the Customers table with row 3 removed. |
Replaces the header row in the given table.
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.
| Rule | Meaning |
|---|---|
| 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"). |
Replaces a row in the given table.
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.
| Rule | Meaning |
|---|---|
| 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"). |
Reverses the order of the rows in the given table.
TableReverse(Table As Table)
Where:
Table is the Table to reverse the row order of.
| Rule | Meaning |
|---|---|
| TableReverse(DWLookupCustomers) | Will reverse the existing order of the rows in the Customers table. |
Constructs a table containing a header row, followed by a single data row containing the given values.
TableRow(Collection of Values As String)
Where:
Collection of Values are the values to include in the data row.
| Rule | Meaning |
|---|---|
| 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. |
Retrieves the columns from the given table in the specified order, and returns the result.
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.
| Rule | Meaning |
|---|---|
| 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. |
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.
TableSequence(Row Count As Double)
Where:
Row Count is the number of data rows to be created in the table.
| Rule | Meaning |
|---|---|
| TableSequence(50) | Will return an array of a single column with 50 rows, each containing the values 1 through to 50. |
Returns a new table where the specified number of rows have been removed from the start of the given table.
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.
| Rule | Meaning |
|---|---|
| TableSkipRows(DWLookupCustomers,14) | Will return an array of the table Customers with the first 14 rows omitted. |
Substitutes text found in a column in the provided table.
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.
| Rule | Meaning |
|---|---|
| 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.". |
Returns a new table with only the specified number of rows.
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.
| Rule | Meaning |
|---|---|
| TableTakeRows(DWLookupCustomers,10) | Will return an array of the table Customers containing the first 10 rows. |
Prepends the given table with a column containing sequence numbers. This is a convenient wrapper around using TableAppendColumns and TableSequence.
TableWithSequence([Table] As Table)
Where:
Table is the table to add a sequence column.
| Rule | Meaning |
|---|---|
| TableWithSequence(DWGroupTableCustomers) | Will return an array of the table Customers with a column added , that has a blank header, which contains sequential numbers. |