Toad Data Point Transformation and Cleanse – Part 2

    This is part 2 of the Transformation and Cleanse videos. In my first video I went over the basics of the window, how to access and process data. In this video I will go over most of the rules. 

The first rule to discuss is the Find and Replace. If you right click on a column and choose Find and Replace the editor will show below and set the column and initial value. You can change the column and value and when ready click apply. This will process the Find and Replace rule on the sample set and add to the step tab.

Next let’s review the filter rule. Here we can add a where condition to filter the data. Pressing preview allows you to see the results before applying the rule. Complex conditions, using more than one filter phrase and operator can be built but you need to be careful to understand how the order of precedence is used in this control.

The format rule allows you to force a string format. You can choose to display numbers with a specific decimal precision and separator. Or the value might be currency and you want to include the currency symbol for your region. Or the value might be a percentage. Processing this rule will change the datatype to a string type. Often formatting is left to a report generator but you may find that this type of formatting helpful in some datasets where you want ensure your end user understands the meaning of the data.  

The Trim rule trims off spaces and hidden characters using the attributes in the editor which are trim beginning, end, or both. Or you can opt to simplify white spaces which preserves a single space between words when there are more than one.

The convert datatype rule allows you to change the resulting exported datatype. This can be used  when you are exporting to Local Storage or publishing to Toad Intelligence Central. There are times where we proactively suggest that you make a datatype change or trim based on the profiling statistics. This will display in red in the profiling pane and a green button is enabled for quickly applying that rule.

Using the group function you can easily define a series of conditionals. It is similar to a case statement but makes it easy to insert your values for grouping so you don’t have to worry about the syntax. This type of rule is also handy to add to the library if you find a need to reuse it over and over again.

There are two more rules which I consider to be advanced rules and these will be covered in our part 3 video.  Those are the calculated rule and duplicates rule.