| D toc |
|---|
| Excerpt |
|---|
Computes an array of integers, from a beginning integer to an end (stop) integer, stepping by a third parameter. |
| Info |
|---|
NOTE: If the function generates more than 100,000 values for a cell, the output is a null value. |
| D s | ||
|---|---|---|
|
Numeric literal example:
| D code |
|---|
derive type:single value: RANGE(0,3,1 ) as: 'threeSteps' |
Output: Generates a column containing the following array:
| Code Block |
|---|
[0,1,2] |
Column reference example:
| D code |
|---|
derive type:single value: RANGE(0,MaxValue,stepValue) as: 'mySteps' |
Output: Generates the mySteps column containing arrays of values from zero to the value in the MaxValue column stepping by the stepValue column value.
| D s | ||
|---|---|---|
|
| D code |
|---|
derive type:single 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 |
| D s lang notes |
|---|
column_integer_start
Name of the column or value of the starting integer used to compute the range.
| Info |
|---|
NOTE: This value is always included in the range, unless it is equal to the value for |
- Missing input values generate missing results.
- Multiple columns and wildcards are not supported.
| D s | ||
|---|---|---|
|
| 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.
| Info |
|---|
NOTE: This value is not included in the output. |
- Missing input values generate missing results.
- Multiple columns and wildcards are not supported.
D s snippet usage
| 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.
| Info |
|---|
NOTE: This value must be a positive integer. If |
- Missing input values generate missing results.
- Multiple columns and wildcards are not supported.
D s snippet usage
| Required? | Data Type | Example Value |
|---|---|---|
| Yes | Integer | 2 |
| D s | ||
|---|---|---|
|
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"] |
Transform:
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.
| D code |
|---|
derive type:single value:ARRAYLEN(Errors) as:'arraylength_Errors' |
This transform deletes rows that contain no errors:
| D code |
|---|
delete row:(arraylength_Errors == 0) |
For the remaining rows, you can generate a column containing an array of numbers to match the count of error messages:
| D code |
|---|
derive type:single value:RANGE(0,arraylength_Errors,1) as:'range_Errors' |
You can then use the ARRAYZIP function to zip together the two arrays into a single one:
| D code |
|---|
derive type:single value:ARRAYZIP([range_Errors,Errors]) as:'zipped_Errors' |
The unnest transform uses the values in an array column as key values to break out rows in your dataset:
| D code |
|---|
unnest col:zipped_Errors |
You might rename the above as individual_Errors. To clean up your dataset, you can now drop the following columns:
arraylength_Errorsrange_Errorszipped_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.
| Include Page | ||||
|---|---|---|---|---|
|
| D s also | ||
|---|---|---|
|
