| 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. |
Numeric literal example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value: RANGE(0,3,1 ) as: 'threeSteps' |
|---|
|
range(0,3,1) |
Output: Returns the following array:
Column reference example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value: RANGE(0,MaxValue,stepValue) as: 'mySteps' |
|---|
|
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.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value: range(column_integer_start, column _integer_end, column_integer_step) |
|---|
|
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.
| Info |
|---|
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.
| Info |
|---|
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.
| Info |
|---|
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.
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'arraylength_Errors' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:arraylen(Errors) as:'arraylength_Errors' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | arraylen(Errors) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
This transform deletes rows that contain no errors:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (arraylength_Errors == 0) |
|---|
| Type | step |
|---|
| WrangleText | delete row:(arraylength_Errors == 0) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Delete matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
For the remaining rows, you can generate a column containing an array of numbers to match the count of error messages:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'range_Errors' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:range(0,arraylength_Errors,1) as:'range_Errors' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | range(0,arraylength_Errors,1) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
You can then use the ARRAYZIP function to zip together the two arrays into a single one:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'zipped_Errors' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:arrayzip([range_Errors,Errors]) as:'zipped_Errors' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | arrayzip([range_Errors,Errors]) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
The unnest transform uses the values in an array column as key values to break out rows in your dataset:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | unnest col:zipped_Errors |
|---|
| p01Name | Column |
|---|
| p01Value | zipped_Errors |
|---|
| SearchTerm | Unnest Objects into columns |
|---|
|
You might rename the above as individual_Errors. To clean up your dataset, you can now delete 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 |
|---|
| EXAMPLE - Flatten and Unnest Transforms |
|---|
| EXAMPLE - Flatten and Unnest Transforms |
|---|
|
