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 next

...

You could use the following transform to flag all rows whose city value equals San Francisco:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:(city == 'San Francisco')
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(city == 'San Francisco')
SearchTermNew formula

Your output looks like the following:

...

You can optionally combine the above with an IF function, which enables you to write values for true or false outcomes:

d-

...

trans
RawWrangletrue
p03Value'BaseballTeam'
Typestep
WrangleTextderive type:single value:

...

if(city == 'San Francisco', 'Home of the Giants!', 'Some other team') as:'BaseballTeam'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueif(city == 'San Francisco', 'Home of the Giants!', 'Some other team')
p03NameNew column name
SearchTermNew formula

Note that the optional as: clause can be used to rename the generated columns. See Derive Transform.

...

Less Than (or Equal To)

Column TypeExample TransformTransformationOutputNotes
Integer

d-

code

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:(MySource < 5)
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(MySource < 5)
SearchTermNew formula

  • true for all values in MySource that are less than 5.
  • Otherwise, false. 
 
Decimal

d-

code

trans
RawWrangletrue
p03Value(MySource <= 2.5)
Typestep
WrangleTextkeep row:(MySource <= 2.5)
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows in the dataset where the value in the MySource column is less than or equal to 2.5. 
Datetime

d-

code

trans
RawWrangletrue
p03Value(Date <= DATE(2009,12,31))
Typestep
WrangleTextkeep row:(Date <= DATE(2009,12,31))
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows whose Date column value is less than or equal to 12/31/2009.You can also use the DATEDIF function to generate the number of days difference between two date values. Then, you can compare this difference to another value. See DATEDIF Function.
String (and all other data types)

d-

code

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:(LEN(MySource) < 5))
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(LEN(MySource) < 5))
SearchTermNew formula

  • true for any string value in the MySource column whose length is less than 5 characters.
  • Otherwise, false
  • See LEN Function.
  • For comparison purposes, all data types not previously listed in this table behave like strings.
  • Since strings are non-numeric value, a function must be applied to string data to render a comparison.

...

Equal to

Column TypeExample TransformTransformationOutputNotes
Integer

d-

code

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:(MySource == 5)
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(MySource == 5)
SearchTermNew formula

  • true for all values in the MySource column that are 5.
  • Otherwise, false.
If the source column contains Decimal values and the right-hand side is an integer value, the Decimal values that are also integers can match in the comparison (e.g. 2.0 == 2).
Decimal

d-

code

trans
RawWrangletrue
p03Value(MySource == 2.5)
Typestep
WrangleTextkeep row:(MySource == 2.5)
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows in the dataset where the value in the MySource column is exactly 2.5.If the source column contains integers and the right-hand side is a Decimal type value, integer values are rounded for comparison.
Datetime

d-

code

trans
RawWrangletrue
p03Value(Date == DATE(2016,12,25))
Typestep
WrangleTextkeep row:(Date == DATE(2016,12,25))
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows whose Date column value is equal to 12/25/2016 . 
String (and all other data types)

d-

code

trans
RawWrangletrue
p03Value(LEN(MySource) == 5))
Typestep
WrangleTextkeep row:(LEN(MySource) == 5))
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows in the dataset where the length of the string value in the MySource column is 5 characters.
  • For comparison purposes, all data types not previously listed in this table behave like strings.
  • Since strings are non-numeric value, a function must be applied to string data to render a comparison.

Not Equal to

Column TypeExample TransformTransformationOutputNotes
Integer

d-

code

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:(MySource <> 5)
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(MySource <> 5)
SearchTermNew formula

  • true for all values in the MySource column that are not 5.
  • Otherwise, false.
If the source column contains Decimal values and the right-hand side is an integer value, the Decimal values that are also integers can match in the comparison (e.g. 2.0 == 2 ).
Decimal

d-

code

trans
RawWrangletrue
p03Value(MySource <> 2.5)
Typestep
WrangleTextkeep row:(MySource <> 2.5)
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows in the dataset where the value in the MySource column is not 2.5.If the source column contains integers and the right-hand side is a Decimal type value, integer values are rounded for comparison.
Datetime

d-

code

trans
RawWrangletrue
p03Value(Date <> DATE(2016,4,15))
Typestep
WrangleTextkeep row:(Date <> DATE(2016,4,15))
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows in the dataset where the Date value does not equal 4/15/2016. 
String (and all other data types)

d-

code

trans
RawWrangletrue
p03Value(LEN(MySource) <> 5))
Typestep
WrangleTextkeep row:(LEN(MySource) <> 5))
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueKeep matching rows
p04NameAction
SearchTermFilter rows

Retains all rows in the dataset where the length of the string value in the MySource column is not 5 characters.
  • For comparison purposes, all data types not previously listed in this table behave like strings.
  • Since strings are non-numeric value, a function must be applied to string data to render a comparison.

...