Blog - https://www.yellowfinbi.com/blog/2018/07/yellowfin...
Yellowfin Bytes – our blog series on the little things that make the biggest impact.
The third edition of Yellowfin Bytes brings you an exciting inclusion made to the Data Transformation module. Users have been exploring Yellowfin’s very own lite version of an ETL tool in the form of this module, ever since its introduction in Yellowfin 7.4 last year. We have since been enhancing this functionality by adding new steps, calculations, configurations, and more.
One of these new enhancements is the ability to quickly find and replace data, specifically text values. This feature parses huge amounts of Strings to identify specific values that are to be replaced with other values or substrings. But this is not your average find and replace tool. What makes this unique, is that it supports the use of regular expressions.
Identifying the values to be replaced
The core of this new feature is replacing and manipulating text values. This is ideal when you need to quickly update incorrect or outdated details, such as employee addresses, emails, or even phone numbers. However, how you specify the values to be selected for replacing is up to you. You can either:
- Enter the full or partial string value to be searched,
- Or use regular expressions (if you’re unfamiliar with this, then this blog includes a quick introduction to this handy pattern-searching syntax).
If you’ve opted to use the good, old fashioned method of entering the text string that needs to be manipulated, then a couple of additional options can be used. One of these includes making the text that you search case sensitive – so that it matches the exact case as specified (that is, the value will not be replaced if both the text and its case don’t match). This can lead to quickly converting text from uppercase to lowercase, or vice versa.
Additionally, when matching values, this feature will look up data similar to the ones you’ve entered, including partial matches to the String. However, you have the power to restrict this so that the value is updated only when the entire string is matched.
So, what are regular expressions?
As mentioned earlier, an alternative means of identifying text values to be replaced via this tool is by using regular expressions. A regular expression (or regex) is a special sequence of characters used to define a search pattern. Unlike most find-and-replace methods that can only find a limited sequence of text or characters, such as a name with the exact spelling, regular expressions can be used to identify unique patterns, such as email addresses, passwords, URLs, etc. Their flexibility makes them a powerful tool when parsing and manipulating data.
The syntax for this can be quite complex, but once learned, can be used anywhere. If you’re unfamiliar with the syntax and would like to learn more, Oracle has a detailed reference guide to get you started. This specific guide is particularly recommended, as our Find & Replace functionality supports the Java regex syntax.
A notably handy situation where regular expression can be used is when you are unsure about the spelling of a word, and the word has multiple spellings. Instead of specifying the word using only one type of spelling, you can identify multiple variations of the same word. For instance, analy[zs]e will look for both variations of the word ‘analyze’, that is with a ‘z’ or an ‘s’.
How to find and replace string values
Here’s what to do when you want to quickly update text values in the Data Transformation module:
- Single out the column that contains the values to be updated in the Data Preview Panel (the bottom left panel that shows the result of each step as it is performed).
- Expand the column menu, by clicking on the arrow next to the column name, and choose Find and Replace.
- In the new popup, you will need to specify which values are to be selected by either providing the text or the regex syntax.
- If using regex to find string values, ensure that the Regular Expression toggle is enabled. Then enter the regular expression syntax in the “Find” field.
- But if searching for values by entering the String itself, disable the regex toggle, and simply enter the text in the same field.
- To look up values with the same case as the String provided, select the Match Case check box.
- And if you’d like to match the entire string as provided (rather than parts of the string), then click on the Match Entire String checkbox.
- In either case of matching, that is via regex or by providing the text itself, enter the value or define the regex syntax that the matched content is to be replaced with in the “Replace with” field.
- The little plus button saves this matching-and-replacing rule. You can continue to include more rules in one go.
- Once you’re ready to replace the data according to these rules, simply click Submit, and watch Yellowfin update the records.
The feature comes equipped with a regex tester and only saves a regex rule if the expression is valid. Otherwise, an error points out to the user, that their syntax has errors.
Allowing for quick data manipulation, this handy feature was just one of many added to Yellowfin in the May the 4th release. Follow this Little Bytes blog series to uncover the many new features that have since been included to enrich the user’s experience and save precious time by minimizing effort.
More from our Yellowfin Bytes blog series: