View Source

Computes an array of integers, from a beginning integer to an end (stop) integer, stepping by a third parameter.

NOTE: If the function generates more than 100,000 values for a cell, the output is a null value.

Numeric literal example:

range(0,3,1 )

Output: Returns the following array:

[0,1,2]

Column reference example:

range(0,MaxValue,stepValue)

Output: Returns an array of values from zero to the value in the MaxValue column stepping by the stepValue column value.

range(column_integer_start, column _integer_end, column_integer_step)

Argument	Required?	Data Type	Description
column_integer_start	Y	string or integer	Name of column or Integer literal that represents the start of the range
column_integer_end	Y	string or integer	Name of column or Integer literal that represents the end of the range
column_integer_step	Y	string or integer	Name of column or Integer literal that represents the steps in integers between values in the range

column_integer_start

Name of the column or value of the starting integer used to compute the range.

NOTE: This value is always included in the range, unless it is equal to the value for col-integer-stop, which results in a blank array.

Missing input values generate missing results.
Multiple columns and wildcards are not supported.

Required?	Data Type	Example Value
Yes	Integer	`0`

column_integer_end

Name of the column or value of the end integer used to compute the range.

NOTE: This value is not included in the output.

Missing input values generate missing results.
Multiple columns and wildcards are not supported.

Required?	Data Type	Example Value
Yes	Integer	`20`

column_integer_step

Name of the column or value of the integer used to compute the integer interval (step) between each value in the range.

NOTE: This value must be a positive integer. If col-integer-start is greater than col-integer-stop, steps are negative values of this parameter.

Missing input values generate missing results.
Multiple columns and wildcards are not supported.

Required?	Data Type	Example Value
Yes	Integer	`2`

Example - Breaking out log messages

Source:

Your dataset contains log data that is gathered each minute, yet each entry can contain multiple error messages in an array. The key fields might look like the following:

Timestamp	Errors
02/16/16 15:31	["Unable to connect","File not found","Proxy down","conn. timeout"]
02/16/16 15:30	[]
02/16/16 15:29	["Access forbidden","Invalid password"]

Transformation:

You can use the following steps to break out the array values into separate rows. The following transform generates a column containing the number of elements in each row's Errors array.

This transform deletes rows that contain no errors:

For the remaining rows, you can generate a column containing an array of numbers to match the count of error messages:

You can then use the ARRAYZIP function to zip together the two arrays into a single one:

The unnest transform uses the values in an array column as key values to break out rows in your dataset:

You might rename the above as individual_Errors. To clean up your dataset, you can now delete the following columns:

arraylength_Errors
range_Errors
zipped_Errors

Results:

Timestamp	Errors	individual_Errors
02/16/16 15:31	["Unable to connect","File not found","Proxy down","conn. timeout"]	[0, "Unable to connect"]
02/16/16 15:31	["Unable to connect","File not found","Proxy down","conn. timeout"]	[1, "File not found"]
02/16/16 15:31	["Unable to connect","File not found","Proxy down","conn. timeout"]	[2, "Proxy down"]
02/16/16 15:31	["Unable to connect","File not found","Proxy down","conn. timeout"]	[3, "conn. timeout"]
02/16/16 15:29	["Access forbidden","Invalid password"]	[0, "Access forbidden"]
02/16/16 15:29	["Access forbidden","Invalid password"]	[1, "Invalid password"]

Example - unnest test scores

The following example includes a range example to define a new index array.