Data steps

Steps are the building blocks of data transformation. They are used in chart queries: each step is a single operation in the query (filtering, aggregating, renaming columns, etc.). Steps run in sequence; the output of one step becomes the input of the next.

The JSON examples in this reference can be copied and adapted for your chart queries. Use the name field to identify the step type and fill in the other properties as described for each step below.

absolutevalue stepCopy Anchor Link

This step is meant to compute the absolute value of a given input column.

{
    "name": "absolutevalue",
    "column": "my-column",
    "newColumn": "my-new-column"
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "absolutevalue",
  "column": "Value",
  "newColumn": "My-absolute-value"
}

Output dataset:

addmissingdates stepCopy Anchor Link

Add missing dates as new rows in a dates column. Exhaustive dates will range between the minimum and maximum date found in the dataset (or in each group if a group by logic is applied - see thereafter).

Added rows will be set to null in columns not referenced in the step configuration.

You should make sure to use a group by logic if you want to add missing dates in independent groups of rows (e.g. you may need to add missing rows for every country found in a "COUNTRY" column). And you should ensure that every date is unique in every group of rows at the specified granularity, else you may get inconsistent results. You can specify "group by" columns in the groups parameter.

An addmissingdates step has the following structure:

{
  "name": "addmissingdates",
  "datesColumn": "DATE",
  "datesGranularity": "day",
  "groups": ["COUNTRY"]
}

Example 1: day granularity without groupsCopy Anchor Link

Input dataset:

Here the day "2018-01-06" is missing.

Step configuration:

{
  "name": "addmissingdates",
  "datesColumn": "DATE",
  "datesGranularity": "day"
}

Output dataset:

Example 2: day granularity with groupsCopy Anchor Link

Input dataset:

Here the day "2018-01-06" is missing for "France" rows, and "2018-01-11" and "2018-01-11" are missing for "USA" rows.

Note that "2018-01-12" will not be considered as a missing row for "France" rows, because the latest date found for this group of rows is "2018-01-11" (even though "2018-01-12" is the latest date found for "USA" rows).

Step configuration:

{
  "name": "addmissingdates",
  "datesColumn": "DATE",
  "datesGranularity": "day",
  "groups": ["COUNTRY"]
}

Output dataset:

Example 3: month granularityCopy Anchor Link

Input dataset:

Here "2019-05" and "2019-11" are missing.

Step configuration:

{
  "name": "addmissingdates",
  "datesColumn": "DATE",
  "datesGranularity": "month"
}

Output dataset:

aggregate stepCopy Anchor Link

Perform aggregations on one or several columns. Available aggregation functions are sum, average, count, count distinct, min, max, first, last.

An aggregation step has the following structure:

{
   "name": "aggregate",
   "on": ["column1", "column2"],
   "aggregations":  [
    {
      "newcolumns": ["sum_value1", "sum_value2"],
      "aggfunction": "sum",
      "columns": ["value1", "value2"]
    },
    {
      "newcolumns": ["avg_value1"],
      "aggfunction": "avg",
      "columns": ["value1"]
    }
  ],
  "keepOriginalGranularity": false
}

Example 1: keepOriginalGranularity set to falseCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "aggregate",
   "on": ["Group"],
   "aggregations":  [
    {
      "newcolumns": ["Sum-Value1", "Sum-Value2"],
      "aggfunction": "sum",
      "columns": ["Value1", "Value2"]
    },
    {
      "newcolumns": ["Avg-Value1"],
      "aggfunction": "avg",
      "columns": ["Value1"]
    }
  ],
  "keepOriginalGranularity": false
}

Output dataset:

Example 2: keepOriginalGranularity set to trueCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "aggregate",
   "on": ["Group"],
   "aggregations":  [
    {
      "newcolumns": ["Total"],
      "aggfunction": "sum",
      "columns": ["Value"]
    }
  ],
  "keepOriginalGranularity": true
}

Output dataset:

append stepCopy Anchor Link

Appends to the current dataset, one or several datasets resulting from other pipelines. WeaverBird allows you to save pipelines referenced by name in the Vuex store of the application. You can then call them by their unique names in this step.

{
  "name": "append",
  "pipelines": ["pipeline1", "pipeline2"]
}

ExampleCopy Anchor Link

Input dataset:

dataset1 (saved in the application Vuex store):

dataset2 (saved in the application Vuex store):

Step configuration:

{
  "name": "append",
  "pipelines": ["dataset1", "dataset2"]
}

Output dataset:

argmax stepCopy Anchor Link

Get row(s) matching the maximum value in a given column, by group if groups is specified.

{
  "name": "argmax",
  "column": "value",
  "groups": ["group1", "group2"]
}

Example 1: without groupsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "argmax",
  "column": "Value"
}

Output dataset:

Example 2: with groupsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "argmax",
  "column": "Value",
  "groups": ["Group"]
}

Output dataset:

argmin stepCopy Anchor Link

Get row(s) matching the minimum value in a given column, by group if groups is specified.

{
  "name": "argmin",
  "column": "value",
  "groups": ["group1", "group2"]
}

Example 1: without groupsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "argmin",
  "column": "Value"
}

Output dataset:

Example 2: with groupsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "argmin",
  "column": "Value",
  "groups": ["Groups"]
}

Output dataset:

comparetext stepCopy Anchor Link

Compares 2 string columns and returns true if the string values are equal, and false oteherwise. The comparison is case-sensitive (see examples below).

{
  "name": "comparetext",
  "newColumnName": "NEW",
  "strCol1": "TEXT_1",
  "strCol2": "TEXT_2"
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "split",
  "newColumnName": "RESULT",
  "ctrCol1": "TEXT_1",
  "ctrCol2": "TEXT_2"
}

Output dataset:

concatenate stepCopy Anchor Link

This step allows to concatenate several columns using a separator.

{
  "name": "concatenate",
  "columns": ["Company", "Group"],
  "separator": " - ",
  "newColumnName": "Label"
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "concatenate",
  "columns": ["Company", "Group"],
  "separator": " - ",
  "newColumnName": "Label"
}

Output dataset:

convert stepCopy Anchor Link

This step allows to convert columns data types.

{
  "name": "convert",
  "columns": ["col1", "col2"],
  "dataType": "integer"

}

In a effort to harmonize as much as possible the conversion behaviors, for some cases, the Sql translator implements casting otherwise than the CAST AS method.

Precisely, when casting float to integer, the default behaviour rounds the result, other languages truncate it. That's why the use of TRUNCATE was implemented when converting float to int. The same implementation was done when converting strings to int (for date represented as string). As for the conversion of date to int, we handled it by assuming the dataset's timestamp is in TIMESTAMP_NTZ format.

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "convert",
  "columns": ["Value"],
  "dataType": "integer"
}

Output dataset:

cumsum stepCopy Anchor Link

This step allows to compute the cumulated sum of value columns based on a reference column (usually dates) to be sorted by ascending order for the needs of the computation. The computation can be scoped by group if needed.

The toCumSum parameter takes as input a list of 2-elements lists in the form ['valueColumn', 'newColumn'].

{
  "name": "cumsum",
  "toCumSum": [["myValues", "myCumsum"]],
  "referenceColumn": "myDates",
  "groupby": ["foo", "bar"]
}

Example 1: Basic usageCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "cumsum",
  "toCumSum": [["VALUE", ""]],
  "referenceColumn": "DATE"
}

Output dataset:

Example 2: With more advanced optionsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "cumsum",
  "toCumSum": [["VALUE", "MY_CUMSUM"]],
  "referenceColumn": "DATE",
  "groupby": ["COUNTRY"]
}

Output dataset:

dateextract stepCopy Anchor Link

Extract date information (eg. day, week, year etc.). The following information can be extracted:

• year: extract 'year' from date,
• month: extract 'month' from date,
• day: extract 'day of month' from date,
• week': extract 'week number' (ranging from 0 to 53) from date,
• quarter: extract 'quarter number' from date (1 for Jan-Feb-Mar)
• dayOfWeek: extract 'day of week' (ranging from 1 for Sunday to 7 for Staurday) from date,
• dayOfYear: extract 'day of year' from date,
• isoYear: extract 'year number' in ISO 8601 format (ranging from 1 to 53) from date.
• isoWeek: extract 'week number' in ISO 8601 format (ranging from 1 to 53) from date.
• isoDayOfWeek: extract 'day of week' in ISO 8601 format (ranging from 1 for Monday to 7 for Sunday) from date,
• firstDayOfYear: calendar date corresponding to the first day (1st of January) of the year ,
• firstDayOfMonth: calendar date corresponding to the first day of the month,
• firstDayOfWeek: calendar date corresponding to the first day of the week,
• firstDayOfQuarter: calendar date corresponding to the first day of the quarter,
• firstDayOfIsoWeek: calendar date corresponding to the first day of the week in ISO 8601 format,
• currentDay: calendar date of the target date,
• previousDay: calendar date one day before the target date,
• firstDayOfPreviousYear: calendar date corresponding to the first day (1st of January) of the previous year,
• firstDayOfPreviousMonth: calendar date corresponding to the first day of the previous month,
• firstDayOfPreviousWeek: calendar date corresponding to the first day of the previous week,
• firstDayOfPreviousQuarter: calendar date corresponding to the first day of the previous quarter,
• firstDayOfPreviousISOWeek: calendar date corresponding to the first day of the previous ISO week,
• previousYear: extract previous 'year number' from date
• previousMonth: extract previous 'month number' from date
• previousWeek: extract previous 'week number' from date
• previousQuarter: extract previous 'quarter number' from date
• previousISOWeek: extract previous 'week number' in ISO 8601 format (ranging from 1 for Monday to 7 for Sunday) from date
• hour: extract 'hour' from date,
• minutes: extract 'minutes' from date,
• seconds: extract 'seconds' from date,
• milliseconds: extract 'milliseconds' from date,

Here's an example of such a step:

{
  "name": "dateextract",
  "column": "date",
  "dateInfo": ["year", "month", "day"],
  "newColumns": ["date_year", "date_month", "date_day"]
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "dateextract",
  "column": "Date",
  "dateInfo": ["year", "month", "day"],
  "newColumns": ["Date_year", "Date_month", "Date_day"]
}

Output dataset:

datetimefromparts stepCopy Anchor Link

Assemble a datetime column from date/time parts stored in separate columns. Each part can come from a column or from a literal integer value shared by every row. Non-integer column values (text, floats) are cast automatically before parsing.

Use this step when your source data stores year, month, and day in separate fields — for example after an export from a legacy database.

{
  "name": "datetimefromparts",
  "newColumnName": "order_date",
  "year": {
    "column": "yr"
  },
  "month": {
    "column": "mo"
  },
  "day": {
    "column": "dy"
  }
}

Parameters

Date part source

Each date/time part (year, month, day, hour, minute, second) is a date part source — either:

• from a column: { "column": "yr" }
• or a literal integer applied to every row: { "value": 2024 }

Example 1: rebuild a date from numeric columns

Input dataset:

Step configuration:

{
  "name": "datetimefromparts",
  "newColumnName": "order_date",
  "year": {
    "column": "yr"
  },
  "month": {
    "column": "mo"
  },
  "day": {
    "column": "dy"
  }
}

Output dataset:

Example 2: fixed year and month names

When the month is stored as text (e.g. March, Jan), use monthFormat to tell the step how to parse it. A literal year applies the same year to every row.

Input dataset:

Step configuration:

{
  "name": "datetimefromparts",
  "newColumnName": "period",
  "year": {
    "value": 2024
  },
  "month": {
    "column": "month_name"
  },
  "monthFormat": "%B"
}

Output dataset:

dategranularity stepCopy Anchor Link

Extract date information (eg. day, week, year etc.) in a column intended for aggregation. The following granularities are supported:

• year: calendar date corresponding to the first day (1st of January) of the year
• quarter: calendar date corresponding to the first day of the quarter
• month: calendar date corresponding to the first day of the month
• week: calendar date corresponding to the first day of the week (sunday)
• isoWeek: calendar date corresponding to the first day of the week (monday)
• day: calendar date corresponding to the first hour of the day

Here's an example of such a step:

{
  "name": "dategranularity",
  "column": "date",
  "granularity": "year",
  "newColumn": "do_the_aggregate_on_this"
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "dategranularity",
  "column": "Date",
  "granularity": "month"
}

Output dataset:

Sub-day granularities

When the source column contains timestamps (not just dates), you can bucket at finer intervals:

• twelveHours: start of the 12-hour period
• sixHours: start of the 6-hour period
• threeHours: start of the 3-hour period
• twoHours: start of the 2-hour period
• hour: start of the hour
• halfHour: start of the 30-minute period
• quarterHour: start of the 15-minute period
• fiveMinutes: start of the 5-minute period
• minute: start of the minute

Each value floors the timestamp to the start of its bucket (useful before an aggregate step).

{
    "name": "dategranularity",
    "column": "event_at",
    "granularity": "hour"
}

delete stepCopy Anchor Link

Delete a column.

{
    "name": "delete",
    "columns": ["my-column", "some-other-column"]
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "delete",
  "columns": ["Company", "Group"]
}

Output dataset:

duplicate stepCopy Anchor Link

This step is meant to duplicate a column.

{
    "name": "duplicate",
    "column": "my-column",
    "newColumnName": "my-duplicate"
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "duplicate",
  "column": "Company",
  "newColumnName": "Company-copy"
}

Output dataset:

duration stepCopy Anchor Link

Compute the duration (in days, hours, minutes or seconds) between 2 dates in a new column.

{
  "name": "duration",
  "newColumnName": "DURATION",
  "startDateColumn": "START_DATE",
  "endDateColumn": "END_DATE",
  "durationIn": "days"
}

Example 1: duration in daysCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "duration",
  "newColumnName": "DURATION",
  "startDateColumn": "START_DATE",
  "endDateColumn": "END_DATE",
  "durationIn": "days"
}

Output dataset:

Example 2: duration in minutesCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "duration",
  "newColumnName": "DURATION",
  "startDateColumn": "START_HOUR",
  "endDateColumn": "END_HOUR",
  "durationIn": "minutes"
}

Output dataset:

evolution stepCopy Anchor Link

Use this step if you need to compute the row-by-row evolution of a value column, based on a date column. It will output 2 columns: one for the evolution in absolute value, the other for the evolution in percentage.

You must be careful that the computation is scoped so that there are no dates duplicates (so that any date finds no more than one previous date). That means that you may need to specify "group by" columns to make any date unique inside each group. You should specify those columns in the indexColumns parameter.

{
  "name": "evolution",
  "dateCol": "DATE",
  "valueCol": "VALUE",
  "evolutionType": "vsLastYear",
  "evolutionFormat": "abs",
  "indexColumns": ["COUNTRY"],
  "newColumn": "MY_EVOL"
}

Example 1: Basic configuration - evolution in absolute valueCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "evolution",
  "dateCol": "DATE",
  "valueCol": "VALUE",
  "evolutionType": "vsLastMonth",
  "evolutionFormat": "abs",
  "indexColumns": []
}

Output dataset:

Example 2: Basic configuration - evolution in percentageCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "evolution",
  "dateCol": "DATE",
  "valueCol": "VALUE",
  "evolutionType": "vsLastMonth",
  "evolutionFormat": "pct",
  "indexColumns": []
}

Output dataset:

Example 3: Error on duplicate datesCopy Anchor Link

If 'COUNTRY' is not specified as indexColumn, the computation will not be scoped by country. Then there are duplicate dates in the "DATE" columns which is prohibited and will lead to an error.

Input dataset:

Step configuration:

{
  "name": "evolution",
  "dateCol": "DATE",
  "valueCol": "VALUE",
  "evolutionType": "vsLastYear",
  "evolutionFormat": "abs",
  "indexColumns": []
}

Output dataset:

Example 4: Complete configuration with index columnsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "evolution",
  "dateCol": "DATE",
  "valueCol": "VALUE",
  "evolutionType": "vsLastYear",
  "evolutionFormat": "abs",
  "indexColumns": ["COUNTRY"],
  "newColumn": "MY_EVOL"
}

Output dataset:

fillna stepCopy Anchor Link

Replace null values by a given value in specified columns.

{
    "name": "fillna",
    "columns": ["foo", "bar"],
    "value": 0
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "fillna",
  "columns": ["Value", "KPI"],
  "value": 0
}

Output dataset:

filter stepCopy Anchor Link

Filter out lines that don't match a filter definition.

{
    "name": "filter",
    "condition": {
      "column": "my-column",
      "value": 42,
      "operator": "ne"
    }
}

operator is optional, and defaults to eq. Allowed operators are eq, ne, gt, ge, lt, le, in, nin, matches, notmatches isnull or notnull.

value can be an arbitrary value depending on the selected operator (e.g a list when used with the in operator, or null when used with the isnull operator).

matches and notmatches operators are used to test value against a regular expression.

Conditions can be grouped and nested with logical operators and and or.

{
    "name": "filter",
    "condition": {
      "and": [
        {
          "column": "my-column",
          "value": 42,
          "operator": "gte"
        },
        {
          "column": "my-column",
          "value": 118,
          "operator": "lte"
        },
        {
          "or": [
            {
              "column": "my-other-column",
              "value": "blue",
              "operator": "eq"
            },
            {
              "column": "my-other-column",
              "value": "red",
              "operator": "eq"
            }
          ]
        }
      ]
    }
}

Relative datesCopy Anchor Link

Date values can be relative to the moment to the moment when the query is executed. This is expressed by using a RelativeDate object instead of the value, of the form:

{
  "quantity": Number,
  "duration": "year" | "quarter" | "month" | "week" | "day"
}

formula stepCopy Anchor Link

Add a computation based on a formula. Usually column names do not need to be escaped, unless they include whitespaces, in which case you'll need to use brackets '[]' (e.g. [myColumn]). Any string escaped with quotes (', ", ''', """) will be considered a string literal.

{
  {
    "name": "formula",
    "newColumn": "result",
    "formula": "(Value1 + Value2) / Value3 - Value4 * 2"
  }
}

Supported operatorsCopy Anchor Link

The following operators are supported by the formula step (note that a value can be a column name or a literal, such as 42 or foo).

• +: Does an addition of two numeric values. See the concatenate step to append strings
• -: Does an substraction of two numeric values. See the replace step to remove a part of a string
• *: Multiplies two numeric values.
• /: Divides a numeric value by another. Divisions by zero will return null.
• %: Returns the rest of an integer division. Divisions by zero will return null.

Example 1: Basic usageCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "formula",
  "newColumn": "Result",
  "formula": "(Value1 + Value2) / Value3 - Value4 * 2"
}

Output dataset:

Example 2: Column name with whitespacesCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "formula",
  "newColumn": "Result",
  "formula": "(Value1 + Value2) / Value3 - [Value 4] * 2"
}

Output dataset:

ifthenelse stepCopy Anchor Link

Creates a new column, which values will depend on a condition expressed on existing columns.

The condition is expressed in the if parameter with a condition object, which is the same object expected by the condition parameter of the filter step). Conditions can be grouped and nested with logical operators and and or.

The then parameter only supports a string, that will be interpreted as a formula (cf. formula step). If you want it to be interpreted striclty as a string and not a formula, you must escape the string with quotes (e.g. '"this is a text"').

if...then...else blocks can be nested as the else parameter supports either a string that will be interpreted as a formula (cf. formula step), or a nested if if...then...else object.

{
  "name": "ifthenelse",
  "newColumn": "",
  "if": { "column": "", "value": "", "operator": "eq" },
  "then": "",
  "else": ""
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
    "name": "ifthenelse",
    "newColumn": "result",
    "if": { "column": "number", "value": 0, "operator": "eq" },
    "then": ""zero""
    "else": {
      "if": { "column": "rel", "value": 0, "operator": "lt" },
      "then": "number * -1",
      "else": "number"
    }
}

Output dataset:

join stepCopy Anchor Link

Joins a dataset to the current dataset, i.e. brings columns from the former into the latter, and matches rows based on columns correspondance. It is similar to a JOIN clause in SQL, or to a VLOOKUP in excel. The joined dataset is the result from the query of the right_pipeline.

The join type can be:

• 'left': will keep every row of the current dataset and fill unmatched rows with null values,
• 'inner': will only keep rows that match rows of the joined dataset.

In the on parameter, you must specify 1 or more column couple(s) that will be compared to determine rows correspondance between the 2 datasets. The first element of a couple is for the current dataset column, and the second for the corresponding column in the right dataset to be joined. If you specify more than 1 couple, the matching rows will be those that find a correspondance between the 2 datasets for every column couple specified (logical 'AND').

Weaverbird allows you to save pipelines referenced by name in the Vuex store of the application. You can then call them by their unique names in this step.

{
  "name": "join",
  "right": {
     "source": {
        "table": {
          "schema": "other_schema",
          "name": "other_table", 
          "columns": ["first_name", "age", "department"]
        }
     },
     "steps": []
  },
  "type": "left",
  "on": [
    ["currentDatasetColumn1", "rightDatasetColumn1"],
    ["currentDatasetColumn2", "rightDatasetColumn2"]
  ]
}

Example 1: Left join with one column couple as on parameterCopy Anchor Link

Input dataset:

rightDataset (saved in the application Vuex store):

Step configuration:

{
  "name": "join",
  "right": {
     "source": {
        "table": {
          "schema": "other_schema",
          "name": "other_table",     
          "columns": ["first_name", "age", "department"],
        }
     },
     "steps": []
  },
  "type": "left",
  "on": [["Label", "Label"]];
}

Output dataset:

Example 2: inner join with different column names in the on parameterCopy Anchor Link

Input dataset:

rightDataset (saved in the application Vuex store):

Step configuration:

{
  "name": "join",
  "right": {
     "source": {
        "table": {
          "schema": "other_schema",
          "name": "other_table",     
          "columns": ["first_name", "age", "department"]
        }
     },
     "steps": []
  },
  "type": "inner",
  "on": [["Label", "LabelRight"]];
}

Output dataset:

fromdate stepCopy Anchor Link

Converts a date column into a string column based on a specified format.

{
    "name": "fromdate",
    "column": "myDateColumn",
    "format": "%Y-%m-%d"

}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "fromdate",
  "column": "Date",
  "format": "%d/%m/%Y"
}

Output dataset:

lowercase stepCopy Anchor Link

Converts a string column to lowercase.

{
  "name": "lowercase",
  "column": "foo"
}

Example:Copy Anchor Link

Input dataset:

Step configuration:

{
  "name": "lowercase",
  "column": "Label"
}

Output dataset:

movingaverage stepCopy Anchor Link

Compute the moving average based on a value column, a reference column to sort (usually a date column) and a moving window (in number of rows i.e. data points). If needed, the computation can be performed by group of rows. The computation result is added in a new column.

{
  "name": "movingaverage",
  "valueColumn": "value",
  "columnToSort": "dates",
  "movingWindow": 12,
  "groups": ["foo", "bar"],
  "newColumnName": "myNewColumn"
}

Example 1: Basic usageCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "movingaverage",
  "valueColumn": "VALUE",
  "columnToSort": "DATE",
  "movingWindow": 2
}

Output dataset:

Example 2: with groups and custom newColumnNameCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "movingaverage",
  "valueColumn": "VALUE",
  "columnToSort": "DATE",
  "movingWindow": 2,
  "groups": ["COUNTRY"],
  "newColumnName": "ROLLING_AVERAGE"
}

Output dataset:

percentage stepCopy Anchor Link

Compute the percentage of total, i.e. for every row the value in column divided by the total as the sum of every values in column. The computation can be performed by group if specified. The result is written in a new column.

{
  "name": "percentage",
  "column": "bar",
  "group": ["foo"],
  "newColumnName": "myNewColumn"
}

Example:Copy Anchor Link

Input dataset:

Step configuration:

{
  "name": "percentage",
  "newColumn": "Percentage_of_total",
  "column": "Value",
  "group": ["Group"],
  "newColumn": "Percentage"
}

Output dataset:

pivot stepCopy Anchor Link

Pivot rows into columns around a given index (expressed as a combination of column(s)). Values to be used as new column names are found in the column column_to_pivot. Values to populate new columns are found in the column value_column. The function used to aggregate data (when several rows are found by index group) must be among sum, avg, count, min or max.

{
 "name": "pivot",
 "index": ["column_1", "column_2"],
 "columnToPivot": "column_3",
 "valueColumn": "column_4",
 "aggFunction": "sum"
}

Example:Copy Anchor Link

Input dataset:

Step configuration:

{
 "name": "pivot",
 "index": ["Label"],
 "columnToPivot": "Country",
 "valueColumn": "Value",
 "aggFunction": "sum"
}

Output dataset:

statistics stepCopy Anchor Link

Compute statistics of a column.,

{
    "name": "statistics",
    "column": "Value",
    "groupby": [],
    "statistics": ["average", "count"],
    "quantiles": [{"label": "median", "nth": 1, "order": 2}]
}

Example:Copy Anchor Link

Input dataset:

Step configuration:

{
    "name": "statistics",
    "column": "Value",
    "groupby": [],
    "statistics": ["average", "count"],
    "quantiles": [{"label": "median", "nth": 1, "order": 2}]
}

Output dataset:

rank stepCopy Anchor Link

This step allows to compute a rank column based on a value column that can be sorted in ascending or descending order. The ranking can be computed by group.

There are 2 ranking methods available, that you will understand easily through those examples:

• standard: input = [10, 20, 20, 20, 25, 25, 30] => ranking = [1, 2, 2, 2, 5, 5, 7]
• dense: input = [10, 20, 20, 20, 25, 25, 30] => ranking = [1, 2, 2, 2, 3, 3, 4]

(The dense method is basically the same as the standard method, but rank always increases by 1 at most).

{
  "name": "rank",
  "valueCol": "VALUE",
  "order": "desc",
  "method": "standard",
  "groupby": ["foo", "bar"],
  "newColumnName": "columnA"
}

Example 1: Basic usageCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "rank",
  "valueCol": "VALUE",
  "order": "desc",
  "method": "standard"
}

Output dataset:

Example 2: With more optionsCopy Anchor Link

Input dataset:

Step configuration:

{
  "name": "rank",
  "valueCol": "VALUE",
  "order": "asc",
  "method": "dense",
  "groupby": ["COUNTRY"],
  "newColumnName": "MY_RANK"
}

Output dataset:

rename stepCopy Anchor Link

Rename one or several columns. The toRename parameter takes as input a list of 2-elements lists in the form ['oldColumnName', 'newColumnName'].

{
    "name": "rename",
    "toRename": [
      ["oldCol1", "newCol1"]
      ["oldCol2", "newCol2"]
    ]
}

Example:Copy Anchor Link

Input dataset:

Step configuration:

{
  "name": "rename",
  "toRename": [["Label", "Company"]]
}

Output dataset:

replace stepCopy Anchor Link

Replace one or several values in a column.

A replace step has the following strucure:

{
   "name": "replace",
   "searchColumn": "column_1",
   "toReplace": [
     ["foo", "bar"],
     [42, 0]
   ]
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
   "name": "replace",
   "searchColumn": "COUNTRY",
   "toReplace": [
     ["Fr", "France"],
     ["UK", "United Kingdom"]
   ]
}

Output dataset:

replacetext stepCopy Anchor Link

Replace a substring in a column.

A replace-text step has the following structure:

{
   "name": "replacetext",
   "searchColumn": "column_1",
   "oldStr": "foo",
   "newStr": "bar"
}

ExampleCopy Anchor Link

Input dataset:

Step configuration:

{
   "name": "replacetext",
   "searchColumn": "COUNTRY",
   "oldStr": "Fr",
   "newStr": "France"
}

Output dataset:

rollup stepCopy Anchor Link

Use this step if you need to compute aggregated data at every level of a hierarchy, specified as a series of columns from top to bottom level. The output data structure stacks the data of every level of the hierarchy, specifying for every row the label, level and parent in dedicated columns.

Aggregated rows can be computed with using either sum, average, count, count distinct, min, max, first or last.

{
   "name": "rollup",
   "hierarchy": ["continent", "country", "city"],
   "aggregations": [
   {
      "newcolumns": ["sum_value1", "sum_value2"],
      "aggfunction": "sum",
      "columns": ["value1", "value2"]
    }
    {
      "newcolumns": ["avg_value1"],
      "aggfunction": "avg",
      "columns": ["value1"]
    }
   ],
   "groupby": ["date"],
   "labelCol": "label",
   "levelCol": "level",
   "childLevelCol": "child_level",
   "parentLabelCol": "parent"
}

Example 1 : Basic configurationCopy Anchor Link

Input dataset:

Step configuration:

{
   "name": "rollup",
   "hierarchy": ["CONTINENT", "COUNTRY", "CITY"],
   "aggregations": [
    {
      "newcolumns": ["VALUE"],
      "aggfunction": "sum",
      "columns": ["VALUE"]
    }
   ]
}

Output dataset:

Example 2 : Configuration with optional parametersCopy Anchor Link

Input dataset:

Step configuration:

{
   "name": "rollup",
   "hierarchy": ["CONTINENT", "COUNTRY", "CITY"],
   "aggregations": [
    {
      "newcolumns": ["VALUE-sum", "COUNT"],
      "aggfunction": "sum",
      "columns": ["VALUE", "COUNT"]
    },
    {
      "newcolumns": ["VALUE-avg"],
      "aggfunction": "avg",
      "columns": ["VALUE"]
    }
   ],
   "groupby": ["YEAR"],
   "labelCol": "MY_LABEL",
   "levelCol": "MY_LEVEL",
   "childLevelCol": "MY_CHILD_LEVEL",
   "parentLabelCol": "MY_PARENT"
}