Page tree

 

Support | BlogContact Us | 844.332.2821

 

Contents:

This documentation applies to Trifacta Wrangler. Download this free product.
Registered users of this product or Trifacta Wrangler Enterprise should login to Product Docs through the application.

Contents:


Returns true if the rightmost set of characters of a column of values matches a pattern. The source value can be any data type, and the pattern can be a Trifacta pattern, regular expression, or a string.

  • The ENDSWITH function is ideal for matching based on patterns for any data type. If you need to match strings using a fixed number of characters, you should use the RIGHT  function instead. See RIGHT Function.
  • See STARTSWITH Function.

Basic Usage

String literal example:

derive value:ENDSWITH(tweets,'?') as:'hasQuestion'

Output: Writes true into the new hasQuestion column if last letter of the tweets column value is "?". 

Trifacta pattern example:

derive value:ENDSWITH(tweets,`{hashtag}{1,9}`) as:'hasHashtag'

Output: Generates the hasHashtag column containing true if the tweets column ends with 1-9 hashtag values. Otherwise, the hasHashtag column is set to false.

Regular expression pattern example:

set col:Status value:IF(ENDSWITH,myNum,/([01][0-9][0-9]|2[0-4][0-9]|25[0-5])/),'myNum - valid','myNum - error')

Output: Sets the value in the Status column to myNum - valid if the value of the myNum column ends with a value between 0-255. Otherwise, the Status column is set to myNum - error.

Syntax

derive value:ENDSWITH(column_any,pattern)

ArgumentRequired?Data TypeDescription
column_anyYanyName of the column to be applied to the function
patternYstringPattern or literal expressed as a string describing the pattern to which to match.

For more information on syntax standards, see Language Documentation Syntax Notes.

column_any

Name of the column to be searched.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesColumn referencemyColumn

pattern

Trifacta pattern, regular expression, or string literal to locate in the values in the specified column.

Usage Notes:

Required?Data TypeExample Value
YesString`{zip}`

Examples

Example - STARTSWITH and ENDSWITH Functions

The following example demonstrates functions that can be used to evaluate the beginning and end of values of any type using patterns. These functions include the following:

  • STARTSWITH - check start of values in a specified column against a specific pattern or literal. See STARTSWITH Function.
  • ENDSWITH - check end of values in a specified column against a specific pattern or literal. See ENDSWITH Function.

Source:

The following inventory report indicates available quantities of product by product name. You need to verify that the product names are valid according to the following rules:

  • A product name must begin with a three-digit numeric brand identifier, followed by a dash.
  • A product name must end with a dash, followed by a six-digit numeric SKU.

Source data looks like the following, with the Validation column having no values in it.

InvDateProductNameQtyValidation
04/21/2017412-Widgets-01234523 
04/21/201704-Fidgets-12034166 
04/21/2017204-Midgets-442131 
04/21/2017593-Gidgets-40201224 

Transform:

In this case, you must evaluate the ProductName column for two conditions. These conditional functions are the following:

IF(STARTSWITH(ProductName, `#{3}-`), 'Ok', 'Bad ProductName-Brand')

IF(ENDSWITH(ProductName, `-#{6}`), 'Ok', 'Bad ProductName-SKU')

One approach is to use the derive transform to create two new test columns and then use a set transform based on the evaluation of these two columns. However, using the following, you can compress the evaluation into a single step without creating the intermediate columns:

set col: Status value: IF(STARTSWITH(ProductName, `#{3}-`), IF(ENDSWITH(ProductName, `-#{6}`), 'Ok', 'Bad ProductName-SKU'), 'Bad ProductName-Brand')

Results:

InvDateProductNameQtyValidation
04/21/2017412-Widgets-01234523Ok
04/21/201704-Fidgets-12034166Bad ProductName-Brand
04/21/2017204-Midgets-442131Bad ProductName-SKU
04/21/2017593-Gidgets-40201224Ok

Your Rating: Results: PatheticBadOKGoodOutstanding! 2 rates

This page has no comments.