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 r078

D toc

Excerpt

The RAND function generates a random real number between 0 and 1. The function accepts an optional integer parameter, which causes the same set of random numbers to be generated with each job execution.

  • This function generates values of Decimal type with fifteen digits of precision after the decimal point. If you want to see all digits in the generated value, you might need to apply a different number format. See NUMFORMAT Function.
  • New random numbers are generated within the browser, after each browser refresh, and between subsequent job executions.

Optionally, you can insert an integer as a parameter.

  • When this value is present, this seed value is used as part of the random number generator such that its output is a set of pseudo-random values, which are consistent between job executions.
  • When the browser is refreshed, the random numbers remain consistent when the seed value is present.
  • This value must be a valid literal Integer value. For more information on valid values, see Integer Data Type.
  • If none is provided, a seed is generated based on the system timestamp.

Column references or functions returning Integer values are not supported. 

D s lang vs sql

D s
snippetBasic

Example:

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value: rand() as:'random'

rand()

Output: For each row, generate a random number between 0 and 1 in the new random function.

Example with seed value:

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value: rand(2) as:'random'

rand(2)

Output: For each row, generate a random number between 0 and 1 in the new random function. The generated random set of random values are consistent between job executions and are, in part, governed by the seed value 2.

D s
snippetSyntax

There are no arguments for this function.

D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value: rand([int_value])

rand([int_value])


ArgumentRequired?Data TypeDescription
int_valueNintegerInteger literal

D s lang notes

int_value

Optional Integer literal that is used to generate random numbers. Use of a seed value ensures consistency of output between job executions.

  • This value must be a valid literal Integer value. For more information on valid values, see Integer Data Type.
  • Literal numeric values should not be quoted. Quoted values are treated as strings.
  • Multiple columns and wildcards are not supported.

D s
snippetusage

Required?Data TypeExample Value
NoInteger literal14

D s
snippetExamples

Example - Random values

In the following example, the random column is generated by the RAND function:

D trans
RawWrangletrue
p03Value'random'
Typestep
WrangleTextderive type:single value:rand() as:'random'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuerand()
p03NameNew column name
SearchTermNew formula

sourcerandom
A

0.516845703365675

B

0.71118736300207

C

0.758686066027118

D

0.640146255791255

Example - Type check functions

The RAND function is typically used to introduce randomness of some kind in your data. In the following example, it is used to perform sampling within your wider dataset. 
Source:

You want to extract a random sample of 20% of your set of orders for further study:

OrderIdQtyProdId
100130Widgets
100210Big Widgets
10035Big Widgets
100440Widgets
100580Tiny Widgets
100620Widgets
1007100Tiny Widgets

Transformation:

You can use the following transform to generate a random integer from one to 10:

D trans
RawWrangletrue
p03Value'random'
Typestep
WrangleTextderive type:single value:round(rand() * 10) as:'random'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueround(rand() * 10)
p03NameNew column name
SearchTermNew formula

You can now use the following transform to keep only the rows that contain random values that are in the top 20%, where the value is 9 or 10:

D trans
RawWrangletrue
p03Value(random > 8)
Typestep
WrangleTextkeep row:(random > 8)
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Results:

Info

NOTE: Since the results are randomized, your results might vary.

OrderIdQtyProdIdrandom
100580Tiny Widgets9
1007100Tiny Widgets10

D s also
labelother