Introduction to Data Workflow Table Operators

Aggregate        

You have a few options for combining elements:

• Sum By: Finds the sum of all numbers for a given range.

• Count By: Counts the total number of values for a given range.

• Mean By: Calculates the average of values for a given range.

• Max By/Min By: Finds the highest or lowest value for a given range.

In Value Key, enter the column header (key) to indicate the range you want to perform your selected operation on.

Append        

Appends two values or arrays. Appending strings is the same as concatenating them. For example, appending "Jane" and "Doe" returns "JaneDoe". When appending arrays, the operator combines them. The data connected to the lower input port appends to the data connected to the upper input port.

This operator won't append objects. To append objects, use the Extend operator.

Cartesian        

This operator takes two arrays and outputs a new array containing each possible combination of the source arrays' values. For example, with source arrays of ["A", "B"] and [1,2], the output becomes [["A",1], ["A",2], ["B",1], ["B",2]]. This operation is similar to performing a Cross Join, or Cartesian Product, in SQL. When the Type is set to Array, the input must be a single array containing two objects. Each object contains one of the arrays you want to cross join.

Clean Keys        

Fills in missing keys or empty values in each row of a table.

• Missing: Creates a key when it's missing from a row. In the Keys field, add the key name you'd like to add. In the Fill With field, enter the value to add to the key.

• Empty: Fills in values for keys that exist but have empty values. In the Keys field, enter the key name you'd like to fill when empty. In the Fill With field, enter the value to add.

• Missing or Empty: Fills values for rows that are missing a key, or that have a key but are missing values.

Col2Array        

Converts a single table column into an array of values. In the Path field, enter the column header (key) of the column you want to convert to an array. For example, with an input of [{"keyName":"hello"},{"keyName":"hi"}], the output is ["hello","hi"] when "keyName" is entered in the Path field.

Convert Field        

Converts all values in all columns of a data set to another data type. You'll input a data set to the operator, indicating which key(s) you want to convert. Then, you'll use the Cast To field to select Number, Integer, or String. Your output is in the form of the selected data type.

Passing an object results in an output of [object,object].

Create Field        

Creates or updates key/value pairs (fields) in a table. Use the structure key=value or key="value" for strings. The operator also supports conditional expressions and referencing other values. Use the structure key=IF(condition,valueIfTrue,valueIfFalse) to create conditional expressions.

The Create Field operations occur in order, from Field 1 to Field 5. You can reference values created in previous fields.

Create Index        

Lets you add index numbers to each table row. It creates a key/value pair, where the value is the index number. In the Index Name field, enter a key name. In the Starting Index field, enter a number to start indexing.

You can also enter _arg in the Starting Index field to reference the input connected to the top argument port.  For example, _arg+1.

The Create Index operator has other uses, including adding new rows of data to a table, starting from either the top or the bottom of the table.

Create Table        

Creates an empty table (array of objects). Use the Number of Rows setting to define how many empty rows (objects) to add. You can set Create Index to Yes to add a key index to each row. The value is an integer value of the index position in the array.

Filter        

Filters table-based data using the criteria specified in the Expression field. The operator separates rows that meet the criteria from rows that do not. Matching rows pass through the upper output port while non-matching rows pass through the lower output port. For example, to filter for rows containing the key/value pair "member":"yes", use the Expression member="yes".

The Expression field also supports AND and OR statements and _arg. Using _arg references data coming into the top argument port.

HTML Table        

Converts a data table to an HTML table. Uses for this operator include displaying the resulting HTML table in a Content component or downloading it in an Excel file.

Map Keys        

Lets you update the names of multiple keys in a table simultaneously. You'll connect your original table to the upper input port. Then, create and connect a Mapping Table to the lower input port. The Mapping Table should contain a column with the original (source) key names and a column with the new (target) key names. Reference these column headers in the operator's Source Column and Target Column fields.

You can use a Data Table component to create your Mapping Table.

Map Values        

Lets you update multiple values in a table simultaneously. Connect your original table to the upper input port. Then, create and connect a Mapping Table to the lower input port. The Mapping Table should contain a column with the original (source) values and a column with the new (target) values. Enter these column headers in the operator's Source Column and Target Column fields.

You can use a Data Table component to create your Mapping Table.

Merge        

Combines data from two data tables. You can merge information from one table into another or combine both tables into an array. In the Merge Key field, enter the reference key of the data you want to merge. This key could be the heading of a column in your data tables. To merge columns with the same values but different keys, enter columnOneName=columnTwoName in the Merge Key field.

Multi-Row        

Duplicates a table column, while shifting the duplicated rows up or down several rows. In the Key Name field, enter the name of the column to duplicate. In the New Key Name field, enter a name for the new column. In the Steps field, enter a positive or negative integer to determine how the rows shift. A positive Steps value shifts rows up based on the integer entered. For example, =1 shifts values up one row. A negative Steps value shifts rows down based on the integer added. For example, =-2 shifts values down two rows. Leaving the Steps value blank results in the values shifting down one row.

Rows left empty after adjacent rows have shifted have an undefined value.

Nest By        

Lets you create a nested table (array of objects) in the connected parent table. In the Parent Keys field, enter the key that remains un-nested in each row. In the Child Name field, enter the key for the nested array where you want it to exist. All other fields in the row nest under the Child Name key. This operator is useful for grouping rows by a common Parent Key value. For example, you can nest by a Parent Key with the same value across multiple rows. All rows with the same Parent Key value group into one row. Their remaining fields nest as individual rows in the Child Name array.

Keys that are not consistent across all arrays are dropped, which can result in data loss.

Nested Array        

Lets you add or update fields in a nested table (array of objects). The input must be a table containing a nested table. In the Table Path field, enter the key that contains the nested table. In the Field 1-4 fields, define the fields to add or update. Use the structure key=value or key="value" for strings.

Omit        

Lets you remove specific columns from a table. In the Omit Keys field, enter the key(s) (column headers) of the column(s) you want to remove from the table. To remove multiple keys, use a comma-separated list.

You can use _arg in the Omit Keys field to reference the input coming into the top argument port. Doing so is useful for dynamically passing a list of keys to omit.

Pick        

Lets you retrieve specific columns from a table. In the Pick Keys field, enter the key(s) (column headers) of the column(s) you want to retrieve from the table. To retrieve multiple keys, use a comma-separated list.

You can use _arg in the Pick Keys field to reference the input coming into the top argument port. Doing so is useful for dynamically passing a list of columns to retrieve from the table.

Rename Field        

Renames keys (fields) of key/value pairs. Enter the current key name in the Current Name field, and the new key name in the Target Name field. The Target Name field supports strings, formulas, and _arg. Using _arg lets you reference the input coming into the top argument port.

Size        

Returns the size of the structure or value passed to the component. If the input is a character string, the operator outputs the number of characters. If the input is an array, the operator outputs the number of elements in the array.

Sort        

Sorts table-based data in ascending or descending order. You can reference _arg in the Sort Keys or Asc/Desc fields to sort based on the input coming into the top argument port. Or, you can hard-code the Sort Keys or Asc/Desc value. To hard-code the Asc/Desc value, enter asc or desc in lowercase. By default, the data sorts in ascending order.

Split Field        

Lets you split values in key/value pair objects, in an array of objects. The split values are added as a nested array of values in each object. In the Path/Field field, enter the key that contains the value you want to split. In the Separator field, enter the separator to split the value on. The separator is not included in the final arrays. In the New Name field, enter a key for the field that contains the array of split values.

You can also use the Split Field operator like the Split String operator, splitting a string input into an array of values.

Table2HTML        

Takes two inputs (an empty table and a value) and outputs a string. The string contains the value, repeated as many times as there are rows in the table. Each value in the string is separated by the HTML break specified in the operator's HTML Break field. You can use a Create Table   operator to create an empty table with as many rows as you want. Then, you can connect the table to the upper input port and the value to the lower input port.

Table2Object        

Converts table-based data into an object. For example, let's say you have a data table with the column headers Key and Value. In the Key Column Name field, you'll enter Key. In Value Column Name, you'll enter Value. Now, each row in the data table becomes a key/value pair in the final object.

What you enter in the Key Column Name and Value Column Name fields must match the table's column headers, including capitalization.

Transpose        

Transposes (rotates) data from columns to rows in a table. Multiple columns can be transposed simultaneously. In the Transpose Keys field, enter the keys (column headers) of the columns you want to transpose into rows.

Unique        

Lets you filter for duplicate key/value pairs in an array of objects and output only the objects with unique values. In the Unique Keys field, enter the key whose unique values you want to filter.

The operator keeps the first instance of any duplicate.

Unwind        

Brings values in a nested array up a level in the data structure. Use the Unwind Key field to define the key that contains the nested array.