Page tree

 

Support | BlogContact Us | 844.332.2821

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • STARTSWITH - check start of values in a specified column against a specific pattern or literal. See TBDSee STARTSWITH Function.
  • ENDSWITH - check end of values in a specified column against a specific pattern or literal. See TBDSee 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:

...

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:

D code
IF(STARTSWITH(ProductName, `#{3}-`), 'Ok', 'Bad ProductName-Brand')
D code
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:
D code
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