Column Reference Syntax
Wrangle enables you to specify sets of columns using discrete values, ranges, and wildcards. This section describes the syntax associated with these various types of references.
Column references can fit into the following categories:
Category | Description |
|---|---|
Single | A reference to a single column. |
Multiple | References to multiple discrete columns. |
All | A single reference to all columns in the dataset. |
Range | References to a set of consecutive columns in the dataset. |
Advanced | Any of the above categories or combinations of them. |
The sections below describe how to specify the above categories of column references in raw Wrangle.
Single and Multiple Columns
You can specify single and multiple columns by inserting discrete references to the column name.
Single column:
Insert the column name in the Columns textbox:
myColumn
Example transformation:
Transformation Name |
|
|---|---|
Parameter: Column(s) | Multiple |
Parameter: Column | myColumn |
Parameter: Option | Before |
Parameter: Column | myFirstColumn |
Multiple columns:
You can reference multiple discrete columns using comma-separated values:
myColumn, myOtherColumn
Example transformation:
Transformation Name |
|
|---|---|
Parameter: Column(s) | Multiple |
Parameter: Column | myColumn,myOtherColumn |
Parameter: Option | Before |
Parameter: Column | myFirstColumn |
All Columns
If needed, you can specify all columns in the dataset using a wildcard. The asterisk character (*) is used to indicate all columns in the dataset:
*
Example transformation:
Transformation Name |
|
|---|---|
Parameter: Column(s) | Advanced |
Parameter: Column | * |
Parameter: Formula | set col: * value: average(myCol) |
The above transformation sets the values for all columns to be the AVERAGE value of the myCol column.
Column Ranges
You can use the tilde character (~) to express a range of columns between the start column and the end column, inclusive:
myStartColumn~myEndColumn
Note
If a transformation step is inserted before this one in which the location of one of the columns in the range is changed, then the columns represented by the specified range changes. If the column is no longer present, then this transformation step must be fixed.
Example transformation:
Transformation Name |
|
|---|---|
Parameter: Column(s) | Advanced |
Parameter: Column | myStartColumn~myEndColumn |
Parameter: Option | Before |
Parameter: Column | myFirstColumn |
Advanced Column References
You can use advanced column references to express combinations of the above types of column reference categories.
myStartColumn~myEndColumn, thisColumn2, thisColumn3
The above example references:
The range of columns between myStartColumn and myEndColumn, inclusive
The thisColumn2 column
The thisColumn3 column
Example transformation:
Transformation Name |
|
|---|---|
Parameter: Column(s) | Advanced |
Parameter: Column | myStartColumn~myEndColumn, thisColumn2, thisColumn3 |
Parameter: Formula | POW($col,2) |
For more information on the $col reference, see below.
Column Variable References
When you are applying a transformation step to multiple columns, you cannot reference each column as a parameter in any function in the transformation. Instead, you can insert a variable reference into the function. Below is an example column variable reference used as the input parameter for the SUM function:
SUM($col)
As the transformation is applied to each column in your column set, the $col reference is replaced with the name of the column.
For more information, see Source Metadata References.