How to work with rules in the Toad Data Point transformation and cleanse utility

This is part two of the transformation and cleanse videos. In my first video I went over the basics of the window, how to access and process data. And in this video, I'm going to go over most of the rules. So let's load up a new data set to do some cleansing on. Now the first rule I want to discuss is a find and replace. So I'm going to do a right click here. And basically I want to find all of the null values and replace it with NA. So you can change the column whenever you want, and enter the values. And then just click Apply. And this will process the find and rule on the sample set. And then go ahead and add it to your steps in the step editor over there. Next let's look at the filter rule. Here we can add a where condition to filter the data. In this case, I think I'm going to look for basically all of the US values and filter them out. Now I can click a Preview here, or I can go ahead and just apply the rule. You can add complex queries by having more than one filter or more than one operator, but you need to be careful to understand how the order of precedence is used in this control. The format rule allows you to format and force an exact string format. So if I click this here, let's say that I want this to be a number with two decimal values and a group of separator of comma. We also load some of the locales because this something that does change based off your regional area. Now this value might also be currency. In this case, we give you a choice of how many decimal digits you want and what the currency symbol would be. The third option would be percentage. And let's do this here. We just click Apply to apply it. Now when you do processes this rule, it's going to change that data type to a string. And this is a hard coded format. You know, often formatting is left to a report generator, but you might find that this type of formatting is helpful in some data sets when you want to ensure your end user understands the significance of the value. Trim is another rule. Let's apply that on this column. It allows you to trim spaces at the beginning, end, or both beginning and end. You can also remove control characters. Or in this case, we can simplify white spaces. Here we have multiple spaces in between the two words. And we really only want one. So this simplifies it to a single one. The convert data type rule allows you to change the resulting exported data type. And this is helpful when you're exporting maybe to local storage or publishing to Toad Intelligence Central. There's also times when the statistics over here are going to proactively suggest this rule. And when that happens, you're going to see in red the values here and a suggestion to say trim or change this particular data type. And you can apply that here. The group function is very easy to use. Comes up as an editor, and you can start defining your values. It's really a case statement underneath. But this way you can really concentrate on what it is that you want to put in without having to pay attention to the actual syntax that's going on underneath. And also, if you have a column that you want to apply a conditional like this, it's handy to apply to the rules editor, so you can reuse it. They can get kind of complex. So those are the rules. There's actually two more which I consider to be advanced rules. I'm going to cover those in a third video. Those are the calculated editor and the duplicates rule.