How to use transform and cleanse in Toad Data Point

Hello, my name is Robert Pound. And I am a product technologist from Quest. In this video, I'd like to talk to you about transform and cleanse in Toad Data Point. Understanding and combining data are two of the major endeavors at the front end of any data analysis task. Toad Data Point addresses these issues in a number of ways. However, the focus of this video will be on transform and cleanse. Highlights of the tool include the profiling of data, including the distribution at the top of each one of the columns, and the tracking of all steps made in the tool so that you can easily change, modify, or delete a step. So now let's take a closer look at the tool. To open up a transform and cleanse, I can choose Transform at the top. I can right click in the Object Explorer on any object and choose Transform and Cleanse, or from the results of a query, by simply right clicking, choosing Send To, and Transform and Cleanse. This will open up a new tab with the data that was selected. To hide or show the columns, I can simply deselect or select the names to the left. In the field itself, as I select the information on the profile changes. So you're looking at data for each one of the columns. And you also have a distribution list at the top of each one of the columns. By clicking on the title of the column, you can sort ascending or descending. And you also can change the profile settings. Currently, it's set by default to look at the first 1,000, but you can modify that as you see fit. And you can also move here to see all the steps. Since this is brand new, there is obviously no steps. Let me switch over briefly to the demo. There is three steps currently here. So as I select them, it's going to show the steps at the bottom, what each one of them is doing. In this case, find and replace. If I wanted to update this, I could modify it, and then choose Update Rule. I could move any one of these rules to the transport repository. And I could also delete any of the steps. Looking at the bottom, the bottom toolbar has all of your output options as far as exporting the data or saving it to local storage. If you have Toad Data Point Professional, you can publish either the data or the file, and you can also send this file to automation or the project manager. And these icons are also in the top toolbar as well. You also have a suggestions. These are auto transforms. So as soon as it's opened and also throughout the process as you add more steps to your transform, the Transform and Cleanse tool will have recommendations for further enhancements to your Transform and Cleanse. And finally looking at the transforms and cleanses themselves. So finding your place is pretty straightforward. For each one of the steps, you'll have to select a column. In this case, I'm going to choose title. And I will choose Mister from the drop down box, and it's going to let me know that it's found several rows. And it's highlighting them for me. So if I wanted to choose the title version of it, I could go ahead and apply this rule. And it will go ahead and find and replace all those values and update the data set. And it's also added a step in the steps list. So looking at the rest of the functions real briefly, there is a calculated column, which allows you to use several different types of functions with the columns to create a new column. You can also further filter this data set by selecting the column that you wish to filter and then choosing the type of filter. You can choose to format any of the columns. Changing the look and feel of that column. De-duplication, so you can choose one or several columns at once to perform a de-duplication on. And then choose the matching method. And then what to do with the data if you find any duplicates. Trim is useful, especially in data entry. If there's errant spaces or tabs in your data fields, and you can't get certain items to match up, you can remove spaces from the beginning and end of your data. You can also remove tabs, new line characters, et cetera. And either in your data set, if it's the wrong data type, or if you want to change the data type for a particular analysis, you can do that here as well. And you can add a grouping to your data set as well as rename any of the columns. You can remove columns, as we talked about before. You can split columns on a particular separator or by position. And finally, you can convert strings into date. And that's the overview of the transform and cleanse. Hopefully, this shed some light on what the transform and cleanse is, what you can do with it. To get more information on this or other topics, you can use the Toad Help file. It has a wide range of information as well as tutorials to get you started. You can also check out the community driven innovation going on at www.toadworld.com. I highly encourage you to log in so you can get content specific to Toad Data Point. For more information on this product, you can visit www.quest.com/products/Toad-Data-Point. Thank you and good luck in all future endeavors.