EXAMPLE - STARTSWITH and ENDSWITH Functions

This example demonstrates functions that can be used to evaluate the beginning and end of values of any type using patterns.

Functions:

Item	Description
STARTSWITH Function	Returns `true` if the leftmost 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 Wrangle , regular expression, or a string.
ENDSWITH Function	Returns`true`ifthe 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 Wrangle , regular expression, or a string.

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.

InvDate	ProductName	Qty
04/21/2017	412-Widgets-012345	23
04/21/2017	04-Fidgets-120341	66
04/21/2017	204-Midgets-4421	31
04/21/2017	593-Gidgets-402012	24

Transformation:

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 create two new test columns and then edit the column 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:

Transformation Name	`Edit column with formula`
Parameter: Columns	Status
Parameter: Formula	IF(STARTSWITH(ProductName, `#{3}-`), IF(ENDSWITH(ProductName, `-#{6}`), 'Ok', 'Bad ProductName-SKU'), 'Bad ProductName-Brand')

Results:

InvDate	ProductName	Qty	Validation
04/21/2017	412-Widgets-012345	23	Ok
04/21/2017	04-Fidgets-120341	66	Bad ProductName-Brand
04/21/2017	204-Midgets-4421	31	Bad ProductName-SKU
04/21/2017	593-Gidgets-402012	24	Ok

In this section:

EXAMPLE - STARTSWITH and ENDSWITH Functions

Search results