Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r079

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 lang vs sql

D s
snippetBasic

Numeric literal example:

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value: RANGE(0,3,1 ) as: 'threeSteps'

range(0,3,1)

Output: Returns the following array:

Code Block
[0,1,2]

Column reference example:

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive 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 s
snippetSyntax

D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value: range(column_integer_start, column _integer_end, column_integer_step)

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"]

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
RawWrangletrue
p03Value'arraylength_Errors'
Typestep
WrangleTextderive type:single value:arraylen(Errors) as:'arraylength_Errors'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuearraylen(Errors)
p03NameNew column name
SearchTermNew formula

This transform deletes rows that contain no errors:

D trans
RawWrangletrue
p03Value(arraylength_Errors == 0)
Typestep
WrangleTextdelete row:(arraylength_Errors == 0)
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter rows

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

D trans
RawWrangletrue
p03Value'range_Errors'
Typestep
WrangleTextderive type:single value:range(0,arraylength_Errors,1) as:'range_Errors'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuerange(0,arraylength_Errors,1)
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'zipped_Errors'
Typestep
WrangleTextderive type:single value:arrayzip([range_Errors,Errors]) as:'zipped_Errors'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuearrayzip([range_Errors,Errors])
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
Typestep
WrangleTextunnest col:zipped_Errors
p01NameColumn
p01Valuezipped_Errors
SearchTermUnnest Objects into columns

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:

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