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 |
ENDSWITH Function | Returns |
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 | Validation |
---|---|---|---|
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 | |
---|---|
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 |