Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

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
snippetBasic

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
snippetSyntax

D code

derive type:single value: RANGE(column_integer_start, column _integer_end, column_integer_step)

ArgumentRequired?Data TypeDescription
column_integer_startYstring or integerName of column or Integer literal that represents the start of the range
column_integer_endYstring or integerName of column or Integer literal that represents the end of the range
column_integer_stepYstring or integerName 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 col-integer-stop, which results in a blank array.

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

D s
snippetusage

 

Required?Data TypeExample Value
YesInteger0

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
snippetusage

Required?Data TypeExample Value
YesInteger20

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.

D s
snippetusage

Required?Data TypeExample Value
YesInteger2

D s
snippetExamples

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:

TimestampErrors
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_Errors
  • range_Errors
  • zipped_Errors

Results:

TimestampErrorsindividual_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

D s also
labelother