Rollup

Block Group:	Table Operations
Icon:

The Rollup block returns a new table that contains one row for each date and time interval in the input table. The output table contains one column for the date and time interval and one column for the value.

For information on using dataflow blocks, see Dataflow.

For answers to some common questions about working with tables, see Tables.

The following properties of the Rollup block can take input and give output.

• input (table)
• interval (enum)
• dateColumn (string)
• valueColumn (string)
• valueRollup (enum)
• roundTime (boolean)
• columnFilterType (enum)
• persistTs (boolean)

input receives the table in which you want to roll up values.

interval specifies the duration of the range to roll up. The default is Twenty minutes. interval can be one of the following:

• One minute
• Five minutes
• Ten minutes
• Fifteen minutes
• Twenty minutes
• Thirty minutes
• Hour
• Two hours
• Three hours
• Four hours
• Six hours
• Eight hours
• Twelve hours
• Day
• Two days
• Week
• Month
• Year

dateColumn specifies the name of the table column that holds the dates.

valueColumn specifies the name of the table column that holds the values to be rolled up. To roll up several columns with the same method, enter their names separated by commas (for example v1,v2).

valueRollup specifies the type of rollup to use. The default is Average. The value of the valueRollup property can be one of the following:

• First: Returns the first value for the interval.
• Last: Returns the last value for the interval.
• Average: Returns the average of all number values for the interval. Non-number values are treated as null and are not included in the calculation.
• Sum: Returns the sum of all number values for the interval. If there are no number values, returns 0.
• Max: Returns the greatest number value from the interval. If there are no number values, returns null.
• Min: Returns the smallest number value for the interval. Non-number values are treated as null and are not included in the calculation.
• Count: Returns the number of values that exist for the interval. Null values are not included.
• Concat: Returns all values for the interval, separated by commas. Duplicate values are included.
• ConcatUnique: Returns all unique values for the interval, separated by commas. Duplicate values are not included.
• Median: Returns the average of the value(s) in the middle row(s) of the interval. Rows are ordered by date, not by value, so this is not a sorted median.

To use a different rollup method or output column name for each value column, use the Advanced Rollup block.

roundTime specifies whether to force all intervals to start at a round number.

• TRUE: Intervals start at a round number. For example, if interval is set to day, all intervals start at midnight. If interval is set to five minutes, all intervals start at multiples of five minutes past the hour.
• FALSE: The first interval starts at the first timestamp, and intervals are appended accordingly. For example, if interval is set to day and the first value is 2015/07/19T08:01:19, then all intervals begin at T08:01:19.

columnFilterType specifies which columns are rolled up. The default is Whitelist.

• Whitelist: Only the columns listed in valueColumn are rolled up.
• Blacklist: All columns except the ones listed in valueColumn, the dateColumn and the row column are rolled up. valueColumn can be empty in this mode.

The resulting table contains all the columns from the input table. Columns that are not rolled up are not removed.

persistTs specifies whether to keep the time zone offset of the input timestamps in the output. If TRUE and the date strings include a time zone offset (for example 2015-07-19T08:00:00.000-07:00), the output dates use the same offset instead of the local time zone.

The input table is sorted by dateColumn before rolling up. Rows whose date cannot be parsed are skipped.

The following property of the Rollup block can give output but cannot take input.

• output (table)

output returns the rolled up table.

The following image shows an example of the Rollup block. In this example, the output table contains the sum value for each week.