Date: Sat, 24 Jul 2021 01:37:07 +0000 (GMT) Message-ID: <184645648.24488.1627090627177@6a789edf488b> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_24487_1435481763.1627090627177" ------=_Part_24487_1435481763.1627090627177 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Changes to the Language

# Changes to the Language

Contents:

=20

=20 =20
=20

=20
=20

The following changes have been applied to  = Wrangle  in this release of Trifacta=C2=AE Wrangler Enterprise.

## Release 6.8

### New Functions

This release introduces the sampling method of calculating statistical f= unctions. The following are now available:

Function Name Description
STD= EVSAMP Function

Computes the standard deviation across column values of Integer or Deci= mal type using the sample statistical method.

VARSA= MP Function

Computes the variance among all values in a column using the samp= le statistical method. Input column can be of Integer or Decimal.&nb= sp;If no numeric values are detected in the input column, the function retu= rns `0`

S= TDEVSAMPIF Function

Generates the standard deviation of values by group in a column that me= et a specific condition using the sample statistical method.

VAR= SAMPIF Function

Generates the variance of values by group in a column that meet a speci= fic condition using the sample statistical method.

ROLLINGSTDEVSAMP Function

Computes the rolling standard deviation of values forward or backward o= f the current row within the specified column using the sample statis= tical method.

ROLLINGVARSAMP Function

Computes the rolling variance of values forward or backward of the curr= ent row within the specified column using the sample statistical meth= od.

## Release 6.6

### New Functions

Function Name Description
SINH Fun= ction

Computes the hyperbolic sine of an input value for a hyperbolic angle m= easured in radians. The value can be a Decimal or Integer literal or a refe= rence to a column containing numeric values.

COSH Function

Computes the hyperbolic cosine of an input value for a hyperbolic angle= measured in radians. The value can be a Decimal or Integer literal or a re= ference to a column containing numeric values.

TANH Function

Computes the hyperbolic tangent of an input value for a hyperbolic angl= e measured in radians. The value can be a Decimal or Integer literal or a r= eference to a column containing numeric values.

ASINH Function

Computes the arcsine of an input value for a hyperbolic angle measured = in radians. The value can be a Decimal or Integer literal or a reference to= a column containing numeric values.

Computes the arccosine of an input value for a hyperbolic angle measure= d in radians. The value can be a Decimal or Integer literal or a reference = to a column containing numeric values.

Computes the arctangent of an input value for a hyperbolic angle measur= ed in radians. The value can be a Decimal or Integer literal or a reference= to a column containing numeric values.

## Release 6.5

### New Functions

Function Name Description
SIN Funct= ion

Computes the sine of an input value for an angle measured in radians. T= he value can be a Decimal or Integer literal or a reference to a column con= taining numeric values.

COS Function

Computes the cosine of an input value for an angle measured in radians.= The value can be a Decimal or Integer literal or a reference to a column c= ontaining numeric values.

TAN Function

Computes the tangent of an input value for an angle measured in radians= . The value can be a Decimal or Integer literal or a reference to a column = containing numeric values.

Cotangent Function See TAN Function.
Secant Function See COS Function.
Cosecant Function See SIN Function.
ASIN Function

For input values between -1 and 1 inclusive, this function returns the = angle in radians whose sine value is the input. This function is the invers= e of the sine function. The value can be a Decimal or Integer literal or a = reference to a column containing numeric values.

ACOS Function

For input values between -1 and 1 inclusive, this function returns the = angle in radians whose cosine value is the input. This function is the inve= rse of the cosine function. The value can be a Decimal or Integer literal o= r a reference to a column containing numeric values.

ATAN Function

For input values between -1 and 1 inclusive, this function returns the = angle in radians whose tangent value is the input. This function is the inv= erse of the tangent function. The value can be a Decimal or Integer literal= or a reference to a column containing numeric values.

Arccotangent Function See ATAN Function.
Arcsecant Function See ACOS Function.
Arccosecant Function See ASIN Function.

## Release 6.4

### Improvemen= ts to metadata references

Broader support for metadata references: For Excel files, ```\$fi= lepath``` references now return the location of the source E= xcel file. Sheet names are appended to the end of the reference. See <= a href=3D"/display/r068/Source+Metadata+References">Source Metadata Referen= ces.

## Release 6.3

### New Functions

Function Name Description
PAR= SEDATE Function

Evaluates an input against an array of Datetime format strings in their= listed order. If the input matches one of the listed formats, the function= outputs a Datetime value.

### Optio= nal input formats for DateFormat task

The DateFormat task now supports a new parameter: Input Formats. This pa= rameter specifies the date format to use when attempting to parse the input= column.

• If the parameter is specified, then the value of the parameter is used = to parse the inputs.
• (default) if the parameter is not speci= fied, then the following common formats are used for parsing the input:

=20
```'M/d/yy',
'MM/dd/yy',
'MM-dd-yy',
'M-d-yy',
'MMM d, yyyy',
'MMMM d, yyyy',
'EEEE, MMMM d, yyyy',
'MMM d yyyy',
'MMMM d yyyy',
'MM-dd-yyyy',
'M-d-yyyy',
'yyyy-MM-ddXXX',
'dd/MM/yyyy',
'd/M/yyyy',
'MM/dd/yyyy',
'M/d/yyyy',
'yyyy/M/d',
'M/d/yy h:mm a',
'MM/dd/yy h:mm a',
'MM-dd-yy h:mm a',
'MMM dd yyyy HH.MM.SS xxx',
'M-d-yy h:mm a',
'MMM d, yyyy h:mm:ss a',
'EEEE, MMMM d, yyyy h:mm:ss a X',
'EEE MMM dd HH:mm:ss X yyyy',
'EEE, d MMM yyyy HH:mm:ss X',
'd MMM yyyy HH:mm:ss X',
'MM-dd-yyyy h:mm:ss a',
'M-d-yyyy h:mm:ss a',
'yyyy-MM-dd h:mm:ss a',
'yyyy-M-d h:mm:ss a',
'yyyy-MM-dd HH:mm:ss.S',
'dd/MM/yyyy h:mm:ss a',
'd/M/yyyy h:mm:ss a',
'MM/dd/yyyy h:mm:ss a',
'M/d/yyyy h:mm:ss a',
'MM/dd/yy h:mm:ss a',
'MM/dd/yy H:mm:ss',
'M/d/yy H:mm:ss',
'dd/MM/yyyy h:mm a',
'd/M/yyyy h:mm a',
'MM/dd/yyyy h:mm a',
'M/d/yyyy h:mm a',
'MM-dd-yy h:mm:ss a',
'M-d-yy h:mm:ss a',
'MM-dd-yyyy h:mm a',
'M-d-yyyy h:mm a',
'yyyy-MM-dd h:mm a',
'yyyy-M-d h:mm a',
'MMM.dd.yyyy',
'd/MMM/yyyy H:mm:ss X',
'dd/MMM/yy h:mm a',```
=20

These formats are a subset of the dat= e formatting strings supported by the product. For more information, see&nb= sp;Datetime Data Type.

=

## Release 6.2

### New Functions

Function Name Description
RANK Fun= ction

Computes the rank of an ordered set of value within groups. Tie values = are assigned the same rank, and the next ranking is incremented by the numb= er of tie values.

DEN= SERANK Function

Computes the rank of an ordered set of value within groups= . Tie values are assigned the same rank, and the next ranking is incremente= d by 1.

### ARRA= YELEMENTAT function accepts new inputs

In previous releases, the ARRAYELEMENTAT function accepted a second inpu= t parameter to specify the index value of the element to retrieve. This "at= " parameter had to be an Integer literal.

Beginning in this release, the function also accepts for this second "at= " parameter:

• Names of columns containing Integer values
• Functions that return Integer values

None.

## Release 6.0

### New Functions

Function Name Description
= ARRAYINDEXOF Function

Computes the index at which a specified element is first found within a= n array. Indexing is left to right.

ARRAYRIGHTINDEXOF Function

Computes the index at which a specified element is first found within a= n array, when searching right to left. Returned value is based on left-to-r= ight indexing.

ARRAYSLICE Function

Returns an array containing a slice of the input array, as determined b= y starting and ending index parameters.

ARRAYMERGEELEMENTS Function

Merges the elements of an array in left to right order into a string. V= alues are optionally delimited by a provided delimiter.

### Changes to LIST* input= s

The following LIST-based functions have been changed to narrow the accep= ted input data types. In previous releases, any data type was accepted for = input, which was not valid for most data types.

In Release 6.0 and later, these functions accept only Array inputs. Inpu= ts can be Array literals, a column of Arrays, or a function returning Array= s.

NOTE: You should references to these functions in your = recipes.

LIST* Functions
L= ISTAVERAGE Function
LISTM= IN Function
LISTM= AX Function
LIST= MODE Function
LIS= TSTDEV Function
LISTSUM Function
LISTVAR Function

### Renamed functions

The following functions have been renamed in Release 6.0.

Release 5.9 and earlier Release 6.0 and later
LISTUNIQUE Function UNIQUE= Function

### = FILL Function has new before and after parameters

Prior to Release 6.0, the FILL function replaced empty cells with the mo= st recent non-empty value.

In Release 6.0, `before` and ```after function parameters have been added. These parameters define the win= dow of rows before and after the row being tested to search for non-empty v= alues. Within this window, the most recent non-empty value is used.  ```

```The default values for these parameters are -1 an= d 0 respectively, which performs a search of an unli= mited number of preceding rows for a non-empty value.```

``` NOTE: Upon upgrade, the FILL function retains its prece= ding behavior, as the default values for the new parameters perform the sam= e unlimited row search for non-empty values. For more information, see F= ILL Function. Release 5.9 New functions The following functions can now be applied directly to arrays to derive = meaningful statistics about them.  Function Description ARR= AYSORT Function Sorts array values in the specified column, array literal, or function = that returns an array in ascending or descending order. TRANSLITERATE Function Transliterates Asian script characters from one script form to another.= The string can be specified as a column reference or a string literal. Release 5.8 File lineage information using source metadata references Beginning in Release 5.8, you can insert the following references into t= he formulas of your transformations. These source metadata ref= erences enable you to continue to track file lineage informat= ion from within your datasets as part of your wrangling project. NOTE: These references apply only to file-based sources= . Some additional limitations may apply. reference Description \$filepath Returns the full path and filename of the source= of the dataset. \$sourcerownumber Returns the row number for the current row from the original source of t= he dataset. NOTE: This reference is equivalent to the SOURCEROWNUMB= ER function, which is likely to be deprecated in a future release. You shou= ld begin using this reference in your recipes.  For more information, see Source Metadata References. New= math and statistical functions for arrays The following functions can now be applied directly to arrays to derive = meaningful statistics about them.  Function Description LISTS= UM Function Computes the sum of all numeric values found in input array. Input can = be an array literal, a column of arrays, or a function returning an array. = Input values must be of Integer or Decimal type. LISTM= AX Function Computes the maximum of all numeric values found in input array. Input = can be an array literal, a column of arrays, or a function returning an arr= ay. Input values must be of Integer or Decimal type. LISTM= IN Function Computes the minimum of all numeric values found in input array. Input = can be an array literal, a column of arrays, or a function returning an arr= ay. Input values must be of Integer or Decimal type. L= ISTAVERAGE Function Computes the average of all numeric values found in input array. Input = can be an array literal, a column of arrays, or a function returning an arr= ay. Input values must be of Integer or Decimal type. LISTV= AR Function Computes the variance of all numeric values found in input array. Input= can be an array literal, a column of arrays, or a function returning an ar= ray. Input values must be of Integer or Decimal type. LIS= TSTDEV Function Computes the standard deviation of all numeric values found in input ar= ray. Input can be an array literal, a column of arrays, or a function retur= ning an array. Input values must be of Integer or Decimal type. LISTMODE Function Computes the most common value of all numeric values found in input arr= ay. Input can be an array literal, a column of arrays, or a function return= ing an array. Input values must be of Integer or Decimal type.   Release 5.7 WEEKNUM function now behaves consistently across runnin= g environments In Release 5.6 and earlier, the WEEKNUM function treated the first week = of the year differently between the Tr= ifacta Photon and Spark running environments: Trifacta Photon wee= k 1 of the year: The week that contains January 1. Spark week 1 of the year: The week that contains = at least four days in the specified year. This issue was caused by Spark following an ISO-8601 standard and relyin= g on the joda datetimeformatter.  Beginning in Release 5.7, the WEEKNUM function behaves consistently for = both Trifacta Photon and S= park: Week 1 of the year: The week that contains Januar= y 1. For more information, see WEEKNUM Function. Release 5.6 URLPARAM= S function returns null values In Release 5.1 and earlier, the URLPARAMS function returned empty Object= s when no answer was computed for the function.  In Release 5.6 and later, this function returns null values in the above= case. See URLPARAMS Function= . Release 5.1 Wrangle  now supports nested expressions Beginning in Release 5.1, all  Wrangle  functions now supported nested expressions, which can be arithmetic= calculations, column references, or other function calls. NOTE: This feature is enabled by default, as this chang= e does not break any steps created in previous versions of the product. It = can be disabled if needed. See Miscellaneous Configuration. NOTE: This capability represents a powerful enhancement= to the language, as you can now use dynamic inputs for all functions. The following expression is a valid transform in  Wrangle . It locates the substring in myString that begins with the @ sign until the end of= the string, inclusive:=20 derive value: substring(myString, find(myString, = '@', true, 0), length(myString) Nested arithmetic expressions: Suppose you wanted just the value after the @ sig= n until the end of the string. Prior to Release 5.1, the following generate= d a validation error:=20 derive value: substring(myString, find(myString, = '@', true, 0) + 1, length(myString) In the above, the addition of +1 to the second pa= rameter is a nested expression and was not supported. Instead, you had to u= se multiple steps to generate the string value.  Beginning in Release 5.1, the above single-step transform is supported.<= /p> Nested column references: In addition to arithmetic expressions, you can nested column references.= In the following example, the previous step has been modified to replace t= he static +1 with a reference to a column containing= the appropriate value (at_sign_offset) : =20 derive value: substring(myString, find(myString, = '@', true, 0) + at_sign_offset, length(myString) Nested function references: ```

```Now, you can combine multiple function references into a single computat= ion. The following computes the total volume of a cube of length side and then multiplies that volume by the number of cubes (<= code>cube_count```) to compute the total `cube_volume`<= /p>=20

derive type: single value: MULTIPLY(POW(cube_side= ,3),cube_count) as: 'cube_volume'

### New Functions

Function Name Description
ARRAYELEMENTAT Function Returns element value of input arr= ay for the provided index value.
DOUBLEMETAPHONE Function Returns primary and secondary phon= etic spellings of an input string using the Double Metaphone algorithm.
DOUBLEMETAPHONEEQUALS Function Returns `true` if two s= trings match phonetic spellings using Double Metaphone algorithm. Tolerance= threshold can be adjusted.
UNIQUE Function Generates a new column containing = an array of the unique values from a source column.

## Release 5.0.1

### RAND= function generates true random numbers

In Release 5.0 and earlier, the RAND function produced the same set of r= andom numbers within the browser, after browser refresh, and over subsequen= t runs of a job.

• During job execution, a default seed value w= as inserted as the basis for the function during the execution of the job.&= nbsp;
• In some cases, this behavior is desired.

In Release 5.0.1 and later, the RAND function accepts an optional intege= r as a parameter. When this new seed value is inserted, the function genera= tes deterministic, pseudo-random values.

• This version matches the behavior of the old function.

NOTE: On all upgraded instances of the platform, refere= nces to the RAND function have been converted to use a default seed value, = so that previous behavior is maintained in the upgraded version.

• If no seed value is inserted as a parameter, the RAND function gener= ates true random values within the browser, after browser refresh, and over= subsequent job runs.

NOTE: Be aware that modifying your dataset based on the= generated values of `RAND()` may have unpredictable effects lat= er in your recipe and downstream of it.

## Release 5.0

### Required type paramet= er

Prior to Release 5.0, the following was a valid  Wrangle  step:

=20

derive value:colA + colB as:'colC'

Beginning in Release 5.0, the `type` parameter is r= equired. This parameter defines whether the transform is a single or multi-= row formula. In the Transform Builder, this value must be specified.

The following is valid in Release 5.0:

=20

derive type:single value:colA + colB as:'colC'

See Derive Transform= .

### Deprecated agg= regate transform

In Release 4.2.1 and earlier, the aggregate transform could be used to a= ggregate your datasets using aggregation functions and groupings.

In Release 5.0 and later, this transform has been merged into the pivot = transform. The aggregate transform has been deprecated and is no longer ava= ilable.

NOTE: During upgrade to Release 5.0 and later, recipes = that had previously used the aggregate transform are automatically migrated= to use the pivot equivalent.

#### Example 1

Release 4.2.1 and earlier Aggregate:

=20

aggregate value:AVERAGE(Scores)

Release 5.0 and later Pivot:

=20

pivot value: AVERAGE(Score) limit: 1

The `limit` parameter defines the maximum number of= columns that can be generated by the pivot.

#### Example 2

Aggregate:

=20

aggregate value:AVERAGE(Scores) group:studentId

Pivot:

=20

pivot group: StudentId value: AVERAGE(Score) limi= t: 1

### New search terms

In the new Search panel, you can search for terms that can be used to se= lect transformations for quick population of parameters. In the following t= able, you can see  Wrangle  how ter= minology has changed in Release 5.0 for some common transforms from earlier= release.

Tip: You can paste the Release 5.0 terms in the Search = panel to locate the same transformations used in earlier releases.

Release 4.2.1 and earlier transforms Release 5.0 and later search terms

aggregate

pivot
keep filter
delete filter
extract on: extractpatterns
extract at: extractpositions
extract before: extractbetweendelimiters
extract after: extractbetweendelimiters
replace on: replacepatterns
replace at: replacepositions
replace before: replacebetweenpatterns
replace after: replacebetweenpatterns
replace from: replacebetweenpatterns
replace to: replacebetweenpatterns
split on: splitpatterns
split delimiters: splitpositions
split every: splitpositions
split positions: splitpositions
split after: splitpatterns
split before: splitpatterns
split from: splitpatterns
split to: splitpatterns

### operator">Support for <> o= perator

Prior to Release 5.0, the following operator was used to test "not equal= " comparisons:

=20
`!=3D`
= =20

Beginning in Release 5.0, the following operators is also supported:

=20
```<>=20
```

Example:

=20

derive value:IF ((col1 <> col2), 'different= ','equal') as:'testNotEqual'

Tip: Both of the above operators are supported, althoug= h the <> operator is preferred.

### RO= UND function takes optional number of digits

The ROUND function now supports rounding to a specified number of digits= . By default, values are rounded to the nearest integer, as before. See&nbs= p;ROUND Function.

### New Functions

Function Name Description
DEGREES Function Generates the value in degrees for= an input radians value.
EXACT F= unction Compares two strings to see if they are exact ma= tches.
FILTEROBJECT Function Filters the keys and values from a= n Object based on specified keys.
HOST Function Returns the host value from a URL.=
ISEVEN Function Returns `true` if an In= teger, function returning an Integer, or a column contains an even value.
ISODD Function Returns `true` if an In= teger, function returning an Integer, or a column contains an odd value.
KTHLARGESTUNIQUE Function Computes the kth-ranked unique val= ue in a set of values.
LCM Function Returns the least common multiple = between two input values.
MODE Function Computes the mode (most common) va= lue for a set of values.
MODEIF Function Computes the mode based on a condi= tional test.
PAD Function Pads the left or right side of a v= alue with a specified character string.
PI Function Generates the value for pi to 15 d= ecimal places.
RADIANS Function Generates the value in radians for= an input degrees value.
RANDBETWEEN Function Generates a random Integer in a ra= nge between two specified values.
RIG= HTFIND Function  Locates a substring by searching from the = right side of an input value.
ROLLINGCOUNTA Function Computes count of non-null values = across a rolling window within a column.
ROLLINGKTHLARGEST Function Computes the kth largest = value across a rolling window within a column.
ROLLINGKTHLARGESTUNIQUE Function Computes the kth largest = unique value across a rolling window within a column.
ROLLINGLIST Function Computes list of all values across= a rolling window within a column.
ROLLINGMAX Function Computes maximum value across a ro= lling window within a column.
ROLLINGMIN Function Computes minimum value across a ro= lling window within a column.
ROLLINGMODE Function Computes mode (most common) value = across a rolling window within a column.
ROLLINGSTDEV Function Computes standard deviation across= a rolling window within a column.
ROLLINGVAR Function Computes variance across a rolling= window within a column.
SIGN Function Computes the positive or negative = sign of an input value.
TRUNC Function Truncates a value to the nearest i= nteger or a specified number of digits.
URLPARAMS Function Extracts any query parameters from= a URL into an Object.
WEEKN= UM Function Calculates the week that the date appears during= the year (1-52).

None.

## Release 4.2

### Rename transform now supports multi-column rename

Use the `rename` transform to rename multiple colum= ns in a single transform.

### New string com= parison functions

Compare two strings using Latin collation settings. See below.

### NOW fun= ction returns 24-hour time values

In Release 4.1.1 and earlier, the NOW function returned time values for = the specified time zone in 12-hour time, which was confusing.

In Release 4.2 and later, this function returns values in 24-hour time.&= nbsp;

### New Transforms

Transform Name Documentation
`case` Case Transform
`filter` Filte= r Transform

### New Functions

Function Name Documentation
`STRINGGREATERTHAN`

STRINGGREATERTHAN Function

`STRINGGREATERTHANEQUAL`

STRINGGREATERTHANEQUAL Function

`STRINGLESSTHAN`

STRINGLESSTHAN Function

`STRINGLESSTHANEQUAL`

STRINGLESSTHANEQUAL Function

`SUBSTITUTE` SU= BSTITUTE Function

------=_Part_24487_1435481763.1627090627177--